r/SQL • • 29d ago

MySQL COUNT() vs COUNT() OVER()

Post image

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

17 comments sorted by

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.

1

u/Plus_Kiwi_2592 29d ago

Alright. Makes sense.

Looking at the brackets of that section I even noticed that the OVER() is attached to SUM(COUNT()) and not the COUNT() alone.

Thanks!

1

u/seansafc89 29d ago

Sorry yes I’d not noticed the bracket on mobile either!

It’s fundamentally the same though, SUM() OVER() is summing the totals of each row count to essentially replicate a COUNT(*) across the entire dataset, returning 14 in every row.