Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Count records per year and month based on date & id

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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);

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading