Number of records created per day
8
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
sql postgresql
asked yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
add a comment |
8
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
sql postgresql
asked yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
1
Did you miss adaycolumn in themessagestable? How do we know what message was sent on which day? Or is your intention to use some metadata from somewhere to combine with records inmessagestables?
– ShellDragon
yesterday
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
yesterday
1
So you want tocountthe number of messagegroup byday...
– AxelH
yesterday
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
yesterday
add a comment |
8
8
8
1
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
sql postgresql
asked yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
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
sql postgresql
sql postgresql
asked yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
asked yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
asked yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
asked yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
asked yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
2,32122570
1
Did you miss adaycolumn in themessagestable? How do we know what message was sent on which day? Or is your intention to use some metadata from somewhere to combine with records inmessagestables?
– ShellDragon
yesterday
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
yesterday
1
So you want tocountthe number of messagegroup byday...
– AxelH
yesterday
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
yesterday
add a comment |
1
Did you miss adaycolumn in themessagestable? How do we know what message was sent on which day? Or is your intention to use some metadata from somewhere to combine with records inmessagestables?
– ShellDragon
yesterday
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
yesterday
1
So you want tocountthe number of messagegroup byday...
– AxelH
yesterday
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
yesterday
1
1
Did you miss a
day column in the messages table? How do we know what message was sent on which day? Or is your intention to use some metadata from somewhere to combine with records in messages tables?– ShellDragon
yesterday
Did you miss a
day column in the messages table? How do we know what message was sent on which day? Or is your intention to use some metadata from somewhere to combine with records in messages tables?– ShellDragon
yesterday
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
yesterday
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
yesterday
1
1
So you want to
count the number of message group by day...– AxelH
yesterday
So you want to
count the number of message group by day...– AxelH
yesterday
2
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
yesterday
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
yesterday
add a comment |
2 Answers
2
active
oldest
votes
5
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?
answered yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
yesterday
add a comment |
3
You could use this:
WITH tmp AS
(
SELECT m.program_id, a.n AS d
FROM generate_series(1,
(SELECT MAX(deliver_day) FROM message_templates)
) AS a(n)
CROSS JOIN
(
SELECT DISTINCT program_id
FROM messages
) m
)
SELECT t.program_id,
t.d AS "day",
COUNT(m.program_id) AS "count" -- COUNT(m.id)
FROM tmp t
LEFT JOIN message_templates mt
ON t.d = mt.deliver_day
LEFT JOIN messages m
ON m.message_template_id = mt.id AND t.program_id = m.program_id
GROUP BY t.program_id, t.d
ORDER BY t.program_id, t.d;
Tested in db-fiddle
answered yesterday
Pham X. BachPham X. Bach
3,72121528
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54436215%2fnumber-of-records-created-per-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
5
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?
answered yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
yesterday
add a comment |
5
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?
answered yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
yesterday
add a comment |
5
5
5
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?
answered yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
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?
answered yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
answered yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
answered yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
answered yesterday
Mateusz UrbańskiMateusz Urbański
2,32122570
2,32122570
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
yesterday
add a comment |
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
yesterday
2
2
Mmmh,
generate_series is a nice features that I didn't knew !– AxelH
yesterday
Mmmh,
generate_series is a nice features that I didn't knew !– AxelH
yesterday
add a comment |
3
You could use this:
WITH tmp AS
(
SELECT m.program_id, a.n AS d
FROM generate_series(1,
(SELECT MAX(deliver_day) FROM message_templates)
) AS a(n)
CROSS JOIN
(
SELECT DISTINCT program_id
FROM messages
) m
)
SELECT t.program_id,
t.d AS "day",
COUNT(m.program_id) AS "count" -- COUNT(m.id)
FROM tmp t
LEFT JOIN message_templates mt
ON t.d = mt.deliver_day
LEFT JOIN messages m
ON m.message_template_id = mt.id AND t.program_id = m.program_id
GROUP BY t.program_id, t.d
ORDER BY t.program_id, t.d;
Tested in db-fiddle
answered yesterday
Pham X. BachPham X. Bach
3,72121528
add a comment |
3
You could use this:
WITH tmp AS
(
SELECT m.program_id, a.n AS d
FROM generate_series(1,
(SELECT MAX(deliver_day) FROM message_templates)
) AS a(n)
CROSS JOIN
(
SELECT DISTINCT program_id
FROM messages
) m
)
SELECT t.program_id,
t.d AS "day",
COUNT(m.program_id) AS "count" -- COUNT(m.id)
FROM tmp t
LEFT JOIN message_templates mt
ON t.d = mt.deliver_day
LEFT JOIN messages m
ON m.message_template_id = mt.id AND t.program_id = m.program_id
GROUP BY t.program_id, t.d
ORDER BY t.program_id, t.d;
Tested in db-fiddle
answered yesterday
Pham X. BachPham X. Bach
3,72121528
add a comment |
3
3
3
You could use this:
WITH tmp AS
(
SELECT m.program_id, a.n AS d
FROM generate_series(1,
(SELECT MAX(deliver_day) FROM message_templates)
) AS a(n)
CROSS JOIN
(
SELECT DISTINCT program_id
FROM messages
) m
)
SELECT t.program_id,
t.d AS "day",
COUNT(m.program_id) AS "count" -- COUNT(m.id)
FROM tmp t
LEFT JOIN message_templates mt
ON t.d = mt.deliver_day
LEFT JOIN messages m
ON m.message_template_id = mt.id AND t.program_id = m.program_id
GROUP BY t.program_id, t.d
ORDER BY t.program_id, t.d;
Tested in db-fiddle
answered yesterday
Pham X. BachPham X. Bach
3,72121528
You could use this:
WITH tmp AS
(
SELECT m.program_id, a.n AS d
FROM generate_series(1,
(SELECT MAX(deliver_day) FROM message_templates)
) AS a(n)
CROSS JOIN
(
SELECT DISTINCT program_id
FROM messages
) m
)
SELECT t.program_id,
t.d AS "day",
COUNT(m.program_id) AS "count" -- COUNT(m.id)
FROM tmp t
LEFT JOIN message_templates mt
ON t.d = mt.deliver_day
LEFT JOIN messages m
ON m.message_template_id = mt.id AND t.program_id = m.program_id
GROUP BY t.program_id, t.d
ORDER BY t.program_id, t.d;
Tested in db-fiddle
answered yesterday
Pham X. BachPham X. Bach
3,72121528
edited yesterday
answered yesterday
Pham X. BachPham X. Bach
3,72121528
answered yesterday
Pham X. BachPham X. Bach
3,72121528
answered yesterday
Pham X. BachPham X. Bach
3,72121528
3,72121528
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54436215%2fnumber-of-records-created-per-day%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Did you miss a
daycolumn in themessagestable? How do we know what message was sent on which day? Or is your intention to use some metadata from somewhere to combine with records inmessagestables?– ShellDragon
yesterday
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
yesterday
1
So you want to
countthe number of messagegroup byday...– AxelH
yesterday
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
yesterday