Postgres - Combine COUNTS and GROUP BY in results table-CodePudding

I have the following table:

CREATE TABLE fruits (
    id INT PRIMARY KEY,
    fruit_name VARCHAR,
    purchase_date DATE
);

The following is data in the table:

 ------------------ 
| Results Table 1a |
 ---- ------------ --------------- 
| id | fruit_name | purchase_date |
 ---- ------------ --------------- 
|  1 | apple      |   2021-02-02  |
|  2 | pear       |   2021-02-02  |
|  3 | orange     |   2021-02-02  |
|  4 | apple      |   2021-02-02  |
|  5 | grapes     |   2021-02-04  |
|  6 | grapes     |   2021-02-05  |
 ---- ------------ ---------------

How can I create a SELECT statement that not only returns the contents of the table like so:

SELECT *
FROM fruits;

But also gives me an additional column that tells me how many purchases were made the same date. I want a results table that looks like this:

 ------------------ 
| Results Table 1b |
 ---- ------------ --------------- -------------------- 
| id | fruit_name | purchase_date | same_day_purchases |
 ---- ------------ --------------- -------------------- 
|  1 | apple      |   2021-02-02  |                  4 |
|  2 | pear       |   2021-02-02  |                  4 |
|  3 | orange     |   2021-02-02  |                  4 |
|  4 | apple      |   2021-02-02  |                  4 |
|  5 | grapes     |   2021-02-04  |                  1 |
|  6 | grapes     |   2021-02-05  |                  1 |
 ---- ------------ --------------- --------------------

Example: there were 4 purchases made on 2021-02-02 so for each purchase_date of that date, the same_day_purchases should be 4.

I can create a separate statement using GROUP BY:

SELECT purchase_date, COUNT(*) AS same_date_purchases
FROM fruits
GROUP BY purchase_date;

But I'm looking for a way to make a statement enables the results table of Results Table 1b.

CodePudding user response：

You may use COUNT window function as the following:

SELECT id, fruit_name, purchase_date,
       COUNT(*) OVER (PARTITION BY purchase_date) AS same_date_purchases
FROM fruits

See a demo.