Over() function does not cover all rows in the table

April 20, 2022

i am practising SQL with MySQL and encounter a strange behaviour in SQL. Say i have a table like this:

Delivery table:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 5           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-11                  |
| 4           | 3           | 2019-08-24 | 2019-08-26                  |
| 5           | 4           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
+-------------+-------------+------------+-----------------------------+

My query:

SELECT COUNT(*) as imm, count(*) over() as all_t 
FROM 
Delivery
WHERE order_date = customer_pref_delivery_date

Result :

+-----+-------+
| imm | all_t |
+-----+-------+
| 2   | 1     |
+-----+-------+

I expect the over() function will cover the whole table, and in this case return 6, but it only returns 1, for some reason. Is there an explanation for this behaviour?

>Solution :

The window function:

count(*) over() as all_t

operates on the results of the query:

SELECT COUNT(*) as imm  
FROM Delivery
WHERE order_date = customer_pref_delivery_date

which is only 1 row (with 1 column) and this is why you get 1 as result.

I believe what you are looking for is conditional aggregation:

SELECT COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 END) AS imm, 
       COUNT(*) AS all_t -- here it's the aggregate function COUNT()
FROM Delivery;

See the demo.