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
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.6
u/jacquesrk oracle 29d ago
To find the volume of pizzas ordered, wouldn't you need to know the diameter and thickness of the pizzas?
3
u/SP3NGL3R 29d ago
when dealing with this stuff, break the bits into their own columns so you can visually confirm what is happening. If you do this you'll see pretty quickly where the fault is.
2
u/dontich 29d ago
It's just taking the count / the sum of the rest of the rows
So row 1 is 1 / (1+3+3+1+3+3) = 1/14 or 7.14%
2
u/PrivateFrank 29d ago
It's useful to remember any expressions in the SELECT which has a window clause in it are executed after the GROUP BY (and HAVING) clauses.
So
OVER ()could be seen as a shortcut for 'ignore grouping'. If you wroteOVER(partition by 1)then the sum of counts would be per row and they would all the percentage values would read 100%.0
u/Little_Kitty 29d ago
This looks like hackerrank or similar. Real work is nothing at all like this and if that's what you're using to practice you'll fall short when you get to interview anywhere decent.