Getting the sum of a value per hour and then getting percentages based on a high and low condition

Ignition
1

I have a query that I want to modify. The current query sums the usage per hour. I want to modify it to be able to just get the percentages of the value greater than 610 and a percentage below 610. Also need to make the two percentages equal 100.

SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
FROM ChlUsage
WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
GROUP BY CONVERT(VARCHAR(13), DateTm, 121)
ORDER BY DateHour desc

current data results

  DateHour    /   TotalUsage
----------------------------
2021-03-16 15 / 197.701171875
2021-03-16 14 / 490.859375
2021-03-16 13 / 578.30078125
2021-03-16 12 / 523.654296875
2021-03-16 11 / 417.15625
2021-03-16 10 / 245.65625
2021-03-16 09 / 406.498046875
2021-03-16 08 / 403.06640625

Desired results

InPermitRangePercentage / OverPermitRangePercentage
           74.70        /           25.30
2

Sorry, I had meant to answer sooner, but I must have seen something shiny to distract me…

After all this time, I guess I don’t know what DB you’re using (sorry), but you can use your existing query as a subquery. Using the ‘1 minus’ for calculating the other percentage helps to guarantee that they add to 100 percent by avoiding any rounding errors.

SELECT SUM(CASE WHEN h.TotalUseage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*) * 100 as InPermitRangePercentage,
       (1-SUM(CASE WHEN h.TotalUseage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*)) * 100 as OverPermitRangePercentage
FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
       FROM ChlUsage
       WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
       GROUP BY CONVERT(VARCHAR(13), DateTm, 121)
       ORDER BY DateHour desc) h
3

I am using SQL Server Lite, soon to I will be upgrading to the full version.

When I ran your query I get this error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

4

Oh! Now that I think about it, try it without the ORDER BY. You wouldn’t really need it in a subquery, anyway.

5

after removing it

I get this error:
Incorrect syntax near ‘)’.

6

Did you take out the whole clause?

SELECT SUM(CASE WHEN h.TotalUseage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*) * 100 as InPermitRangePercentage,
       (1-SUM(CASE WHEN h.TotalUseage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*)) * 100 as OverPermitRangePercentage
FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
       FROM ChlUsage
       WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
       GROUP BY CONVERT(VARCHAR(13), DateTm, 121)) h
7

just the order by, take out the group by as well?

8

The group by should still work.

9

error was just the name 'TotalUseage' Had to change it to 'TotalUsage'

1 Like
10

that worked!

1 Like
11

Is the Greater than or equal to for each one supposed to be the same?

SELECT SUM(CASE WHEN h.TotalUsage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*) * 100 as InPermitRangePercentage,
       (1-SUM(CASE WHEN h.TotalUsage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*)) * 100 as OverPermitRangePercentage
FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
       FROM ChlUsage
       WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
       GROUP BY CONVERT(VARCHAR(13), DateTm, 121)) h
12

Yep. That way the calculation is the same, the second one is 1 minus the calculation to get the other side.

You could certainly try it this way, my only caveat would entail any rounding errors so that the two don’t get to 100%

SELECT SUM(CASE WHEN h.TotalUsage <= 610 THEN 1.0 ELSE 0.0 END)/COUNT(*) * 100 as InPermitRangePercentage,
       SUM(CASE WHEN h.TotalUsage > 610 THEN 1.0 ELSE 0.0 END)/COUNT(*)) * 100 as OverPermitRangePercentage
FROM (SELECT CONVERT(VARCHAR(13), DateTm, 121) DateHour, SUM(Usage) TotalUsage
       FROM ChlUsage
       WHERE DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
       GROUP BY CONVERT(VARCHAR(13), DateTm, 121)) h
13

ok, I tried it but the results is the same. I will stick with doing it the way you posted the solution. Just wanted to make sure the data I would be displaying was correct. Thanks alot for the help!

14

Since they’re double precsion, any rounding errors would likely be negligible. The subtraction method is one I use starting WAAAY back in 16-bit days. lol