Postgres – Combine COUNTS and GROUP BY in results table

https://dbfiddle.uk/z01O9eNR

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.

>Solution :

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.