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

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?

CodePudding user response：

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.

CodePudding user response：

The SELECT COUNT(*) counts rows that satisfy the conditions in the WHERE clause. There are two such rows.

The query with an aggregate function but no GROUP BY returns a single row, to report the count. The window function then applies to that result set, and counts 1 row. In other words, window functions apply to the set of rows after aggregation has reduced the number of rows.

You can test this by omitting the aggregation:

mysql> SELECT count(*) over() as all_t  FROM  Delivery WHERE order_date = customer_pref_delivery_date;
 ------- 
| all_t |
 ------- 
|     2 |
|     2 |
 -------

Or without the conditions of the WHERE clause, it returns the count of rows for each of the set of six rows:

mysql> SELECT count(*) over() as all_t  FROM  Delivery;
 ------- 
| all_t |
 ------- 
|     6 |
|     6 |
|     6 |
|     6 |
|     6 |
|     6 |
 -------