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 Jan 30 at 8:30
Mateusz UrbańskiMateusz Urbański
2,37122771
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 Jan 30 at 8:30
Mateusz UrbańskiMateusz Urbański
2,37122771
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
Jan 30 at 8:38
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
Jan 30 at 8:41
1
So you want tocountthe number of messagegroup byday...
– AxelH
Jan 30 at 8:42
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
Jan 30 at 8:57
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 Jan 30 at 8:30
Mateusz UrbańskiMateusz Urbański
2,37122771
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 Jan 30 at 8:30
Mateusz UrbańskiMateusz Urbański
2,37122771
asked Jan 30 at 8:30
Mateusz UrbańskiMateusz Urbański
2,37122771
asked Jan 30 at 8:30
Mateusz UrbańskiMateusz Urbański
2,37122771
asked Jan 30 at 8:30
Mateusz UrbańskiMateusz Urbański
2,37122771
asked Jan 30 at 8:30
Mateusz UrbańskiMateusz Urbański
2,37122771
2,37122771
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
Jan 30 at 8:38
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
Jan 30 at 8:41
1
So you want tocountthe number of messagegroup byday...
– AxelH
Jan 30 at 8:42
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
Jan 30 at 8:57
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
Jan 30 at 8:38
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
Jan 30 at 8:41
1
So you want tocountthe number of messagegroup byday...
– AxelH
Jan 30 at 8:42
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
Jan 30 at 8:57
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
Jan 30 at 8:38
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
Jan 30 at 8:38
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
Jan 30 at 8:41
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
Jan 30 at 8:41
1
1
So you want to
count the number of message group by day...– AxelH
Jan 30 at 8:42
So you want to
count the number of message group by day...– AxelH
Jan 30 at 8:42
2
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
Jan 30 at 8:57
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
Jan 30 at 8:57
add a comment |
2 Answers
2
active
oldest
votes
6
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 Jan 30 at 8:47
Mateusz UrbańskiMateusz Urbański
2,37122771
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
Jan 30 at 8:50
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 Jan 30 at 8:48
Pham X. BachPham X. Bach
3,83821529
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
6
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 Jan 30 at 8:47
Mateusz UrbańskiMateusz Urbański
2,37122771
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
Jan 30 at 8:50
add a comment |
6
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 Jan 30 at 8:47
Mateusz UrbańskiMateusz Urbański
2,37122771
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
Jan 30 at 8:50
add a comment |
6
6
6
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 Jan 30 at 8:47
Mateusz UrbańskiMateusz Urbański
2,37122771
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 Jan 30 at 8:47
Mateusz UrbańskiMateusz Urbański
2,37122771
answered Jan 30 at 8:47
Mateusz UrbańskiMateusz Urbański
2,37122771
answered Jan 30 at 8:47
Mateusz UrbańskiMateusz Urbański
2,37122771
answered Jan 30 at 8:47
Mateusz UrbańskiMateusz Urbański
2,37122771
2,37122771
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
Jan 30 at 8:50
add a comment |
2
Mmmh,generate_seriesis a nice features that I didn't knew !
– AxelH
Jan 30 at 8:50
2
2
Mmmh,
generate_series is a nice features that I didn't knew !– AxelH
Jan 30 at 8:50
Mmmh,
generate_series is a nice features that I didn't knew !– AxelH
Jan 30 at 8:50
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 Jan 30 at 8:48
Pham X. BachPham X. Bach
3,83821529
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 Jan 30 at 8:48
Pham X. BachPham X. Bach
3,83821529
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 Jan 30 at 8:48
Pham X. BachPham X. Bach
3,83821529
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 Jan 30 at 8:48
Pham X. BachPham X. Bach
3,83821529
edited Jan 30 at 9:11
answered Jan 30 at 8:48
Pham X. BachPham X. Bach
3,83821529
answered Jan 30 at 8:48
Pham X. BachPham X. Bach
3,83821529
answered Jan 30 at 8:48
Pham X. BachPham X. Bach
3,83821529
3,83821529
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
Jan 30 at 8:38
Information about on which day message was sent is in message_templates delivery_day column.
– Mateusz Urbański
Jan 30 at 8:41
1
So you want to
countthe number of messagegroup byday...– AxelH
Jan 30 at 8:42
2
@AxelH The main problem he wanted to address was missing days in between. I missed it
– ShellDragon
Jan 30 at 8:57