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

Question

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?

Answer 1

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.