Number of records created per day

  • A+
Category:Languages

In my PostgreSQL database I have the following schema:

CREATE TABLE programs (     id integer,     description text );  CREATE TABLE public.messages (     id integer,     program_id integer,     text text,     message_template_id integer );  CREATE TABLE public.message_templates (     id integer,     deliver_day integer );  INSERT INTO programs VALUES(1, 'Test program');  INSERT INTO messages VALUES(1,1, 'Test message 1', 1); INSERT INTO message_templates VALUES(1, 1);  INSERT INTO messages VALUES(2,1, 'Test message 2', 2); INSERT INTO message_templates VALUES(2, 3);  INSERT INTO messages VALUES(3,1, 'Test message 3', 3); INSERT INTO message_templates VALUES(3, 5); 

Now I want to get number of message sent per day throughout the life of the program, query result should look like this:

  day      count --------|----------    1         1    2         0    3         1    4         0    5         1 

Is there any way of doing that in PostgreSQL?

https://www.db-fiddle.com/f/gvxijmp8u6wr6mYcSoAeVV/2

 


I decided to use generate_series:

SELECT d AS "Day", count(mt.id)  FROM generate_series(   (SELECT min(delivery_day) from message_templates),   (SELECT max(delivery_day) from message_templates) ) d left join message_templates mt on mt.delivery_day = d  group by d.d 

Query is working fine. Maybe there is better way of doing this?

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: