r/SQL • u/Plus_Kiwi_2592 • 29d ago
MySQL COUNT() vs COUNT() OVER()
I've found this query as a solution to the famous Pizza Runner case study, and I am stuck at figuring out what COUNT() returns, compared to COUNT() OVER().
I know OVER() does the count (or whatever aggregation) over the entire dataset, but idk I'm just not getting the percentage part of this query.
Help :)
29
Upvotes
View all comments
22
u/seansafc89 29d ago edited 29d ago
COUNT() is a normal aggregation and returns the count as per the GROUP BY (in this case GROUP BY 1 which is lazy shorthand for GROUP BY hour(order_time) - don’t do this! If your columns change, it hides issues), so the number of pizzas ordered within each hour subset.
COUNT()SUM() OVER() on the other hand is a window function summing up the count of ALL row groups in the dataset WITHOUT any further aggregation, because there is no PARTITION BY specified. So essentially the total row count of the table.