Count records per year and month based on date & id-CodePudding

I have the following table.

date       location_id    type_id   other_id   
01/01/2021       1           22       3         
01/01/2021       1           22       5         
01/01/2021       2            1       1         
15/01/2021       2            1       1         
22/01/2021       1           22       1         
01/02/2021       1            1       1   
01/02/2021       1           22       1

I would like to count the number of records per year/month based on location_id and type_id. My desired output should be like this:

date       location_id    type_id   other_id   count
01/01/2021       1           22       3         3
01/01/2021       1           22       5         3
01/01/2021       2            1       1         2
15/01/2021       2            1       1         2
22/01/2021       1           22       1         3
01/02/2021       1            1       1         1
01/02/2021       1           22       1         1

What I have tried:

select year(day_dt), month(day_dt), location_id, type_id count(1) as nb_record
from preliminar 
group by year(day_dt), month(day_dt), location_id, type_id

But the issue with this query, is that I don't get the count for each initial rows in my table and I lost the day information.

Any help would be appreciated

Data using MySQL/HiveQL

CREATE TABLE preliminar (
  day_dt date NOT NULL,
  location_id int,
  type_id int,
  other_id int);

INSERT INTO preliminar 
    (day_dt, location_id,type_id, other_id) 
VALUES 
    ('2021-01-01',1, 22, 3),
    ('2021-01-01',1, 22, 5),
    ('2021-01-01',2,  1, 1),
    ('2021-01-15',2,  1, 1),
    ('2021-01-22',1,  22, 1),
    ('2021-02-01',1,  1, 1),
    ('2021-02-01',1,  22, 1);

CodePudding user response：

Using MySQL 8.0 window functions

SELECT *, COUNT(*) OVER(PARTITION BY location_id, type_id, YEAR(day_dt), MONTH(day_dt)) counts
FROM preliminar
ORDER BY day_dt

db<>fiddle