On 2017/01/25 7:13 AM, Jeffrey Mattox wrote:
I use this query to get a grand total of a number of counts:
(1) SELECT TOTAL(count) as grandTotalCount FROM History
Next, I step through the counts, grouped by column 'hour:
(2a) SELECT TOTAL(count) as subTotalCount, hour FROM History GROUP BY hour
I use grandTotalCount in the loop to compute percentages of the whole for each
subTotalCount. I also need to scale (normalize) the percentages, and for that
I need to know the largest subTotalCount in advance of the loop. I can do that
by ordering the results by the subtotals and saving the subTotalCount of the
first result row as largestSubTotalCount:
(2b) SELECT TOTAL(count) as subTotalCount, hour FROM History GROUP BY hour
ORDER BY 1 DESC
But, I'd prefer that last query be "ORDER BY hour", and thus I need a separate
query to get the largest subTotalCount. This works:
(3) SELECT TOTAL(count) as largestSubTotalCount FROM History GROUP BY hour
ORDER BY 1 DESC LIMIT 1
Can (1) and (3) be combined to return grandTotalCount and largestSubTotalCount?
WITH SC(subTotCount, subHour) AS (
SELECT TOTAL(count), hour FROM History GROUP BY hour
), TOT(grandTotCount, largestSubTotCount) AS (
SELECT TOTAL(subTotCount), MAX(subTotCount) FROM SC
)
SELECT subTotCount, (subTotCount / grandTotCount) AS PercOfTot,
(subTotCount / largestSubTotCount) AS PercNormalized, subHour
FROM SC, TOT
ORDER BY subHour
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
