PostgreSQL row not shown when count is zero2019 Community Moderator ElectionPostgreSQL “DESCRIBE TABLE”Inserting multiple rows in a single SQL query?Show tables in PostgreSQLCreating a copy of a database in PostgreSQLInsert, on duplicate update in PostgreSQL?Save PL/pgSQL output from PostgreSQL to a CSV fileHow can I drop all the tables in a PostgreSQL database?Select first row in each GROUP BY group?How to exit from PostgreSQL command line utility: psqlWhich version of PostgreSQL am I running?
What Happens when Passenger Refuses to Fly Boeing 737 Max?
What wound would be of little consequence to a biped but terrible for a quadruped?
Virginia employer terminated employee and wants signing bonus returned
How are showroom/display vehicles prepared?
Single word request: Harming the benefactor
How is the wildcard * interpreted as a command?
Accountant/ lawyer will not return my call
How can I get players to stop ignoring or overlooking the plot hooks I'm giving them?
PTIJ: wiping amalek’s memory?
Am I not good enough for you?
Why was Goose renamed from Chewie for the Captain Marvel film?
Is it possible to avoid unpacking when merging Association?
Makefile strange variable substitution
Find longest word in a string: are any of these algorithms good?
Do recommendation systems necessarily use machine learning algorithms?
Do items de-spawn in Diablo?
Why the color red for the Republican Party
Intuition behind counterexample of Euler's sum of powers conjecture
What are actual Tesla M60 models used by AWS?
'The literal of type int is out of range' con número enteros pequeños (2 dígitos)
When a wind turbine does not produce enough electricity how does the power company compensate for the loss?
Can one live in the U.S. and not use a credit card?
Is "conspicuously missing" or "conspicuously" the subject of this sentence?
Does "Until when" sound natural for native speakers?
PostgreSQL row not shown when count is zero
2019 Community Moderator ElectionPostgreSQL “DESCRIBE TABLE”Inserting multiple rows in a single SQL query?Show tables in PostgreSQLCreating a copy of a database in PostgreSQLInsert, on duplicate update in PostgreSQL?Save PL/pgSQL output from PostgreSQL to a CSV fileHow can I drop all the tables in a PostgreSQL database?Select first row in each GROUP BY group?How to exit from PostgreSQL command line utility: psqlWhich version of PostgreSQL am I running?
0
I am trying to output total number of orders placed by customers of each country in the year 2018. Here's my query:
select country, count(country)
from customers, orders
where customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by(country);
This prints out the correct values but countries with 0 orders are not outputted. I tried different types of joins as well (right join, left etc.) but still no luck. Anyone know how to fix this or know what might be wrong with it?
sql postgresql
asked Mar 7 at 6:48
tirth96tirth96
114
add a comment |
0
I am trying to output total number of orders placed by customers of each country in the year 2018. Here's my query:
select country, count(country)
from customers, orders
where customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by(country);
This prints out the correct values but countries with 0 orders are not outputted. I tried different types of joins as well (right join, left etc.) but still no luck. Anyone know how to fix this or know what might be wrong with it?
sql postgresql
asked Mar 7 at 6:48
tirth96tirth96
114
Tip of today: Always use modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Mar 7 at 7:35
add a comment |
0
0
0
I am trying to output total number of orders placed by customers of each country in the year 2018. Here's my query:
select country, count(country)
from customers, orders
where customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by(country);
This prints out the correct values but countries with 0 orders are not outputted. I tried different types of joins as well (right join, left etc.) but still no luck. Anyone know how to fix this or know what might be wrong with it?
sql postgresql
asked Mar 7 at 6:48
tirth96tirth96
114
I am trying to output total number of orders placed by customers of each country in the year 2018. Here's my query:
select country, count(country)
from customers, orders
where customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by(country);
This prints out the correct values but countries with 0 orders are not outputted. I tried different types of joins as well (right join, left etc.) but still no luck. Anyone know how to fix this or know what might be wrong with it?
sql postgresql
sql postgresql
asked Mar 7 at 6:48
tirth96tirth96
114
asked Mar 7 at 6:48
tirth96tirth96
114
asked Mar 7 at 6:48
tirth96tirth96
114
asked Mar 7 at 6:48
tirth96tirth96
114
asked Mar 7 at 6:48
tirth96tirth96
114
114
Tip of today: Always use modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Mar 7 at 7:35
add a comment |
Tip of today: Always use modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Mar 7 at 7:35
Tip of today: Always use modern, explicit
JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Mar 7 at 7:35
Tip of today: Always use modern, explicit
JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Mar 7 at 7:35
add a comment |
2 Answers
2
active
oldest
votes
1
use modern join not coma separated join, do count for orders.id
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and orders.date <='2018-12-31'
group by country
You don't need parenthesis after group by
answered Mar 7 at 6:49
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.1k21033
I tried left and right join, still doesn’t work
– tirth96
Mar 7 at 6:57
@tirth96 create table here in fiddle dbfiddle.uk/… it should work if not then you need to help us by creating fiddle then we can solve your problem
– Zaynul Abadin Tuhin
Mar 7 at 6:58
ok nvm this worked, do you know it can be done with just join, not left join
– tirth96
Mar 7 at 7:08
@tirth96 it will not work just by using join because with out left join you will loose the country from where still not ordered place
– Zaynul Abadin Tuhin
Mar 7 at 7:12
1
That LEFT JOIN returns regular INNER JOIN result. Move the orders.date conditions from WHERE to ON to get true LEFT JOIN result.
– jarlh
Mar 7 at 8:16
|
show 3 more comments
2
use left join and move your where condition in ON clause
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by country
answered Mar 7 at 6:49
fa06fa06
16.2k21018
Tried that, still no luck
– tirth96
Mar 7 at 6:58
1
@tirth96: that should do exactly what you want. You need to edit and provide some sample data and the expected output on that data. Or create a short online example, e.g. here or here
– a_horse_with_no_name
Mar 7 at 7:07
ooh i see, this worked!
– tirth96
Mar 7 at 7:09
Do you know how I can do the same thing with just join?
– tirth96
Mar 7 at 7:09
@tirth96, you've to use left join - with inner join it's not possible
– fa06
Mar 7 at 7:10
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%2f55037647%2fpostgresql-row-not-shown-when-count-is-zero%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
1
use modern join not coma separated join, do count for orders.id
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and orders.date <='2018-12-31'
group by country
You don't need parenthesis after group by
answered Mar 7 at 6:49
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.1k21033
I tried left and right join, still doesn’t work
– tirth96
Mar 7 at 6:57
@tirth96 create table here in fiddle dbfiddle.uk/… it should work if not then you need to help us by creating fiddle then we can solve your problem
– Zaynul Abadin Tuhin
Mar 7 at 6:58
ok nvm this worked, do you know it can be done with just join, not left join
– tirth96
Mar 7 at 7:08
@tirth96 it will not work just by using join because with out left join you will loose the country from where still not ordered place
– Zaynul Abadin Tuhin
Mar 7 at 7:12
1
That LEFT JOIN returns regular INNER JOIN result. Move the orders.date conditions from WHERE to ON to get true LEFT JOIN result.
– jarlh
Mar 7 at 8:16
|
show 3 more comments
1
use modern join not coma separated join, do count for orders.id
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and orders.date <='2018-12-31'
group by country
You don't need parenthesis after group by
answered Mar 7 at 6:49
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.1k21033
I tried left and right join, still doesn’t work
– tirth96
Mar 7 at 6:57
@tirth96 create table here in fiddle dbfiddle.uk/… it should work if not then you need to help us by creating fiddle then we can solve your problem
– Zaynul Abadin Tuhin
Mar 7 at 6:58
ok nvm this worked, do you know it can be done with just join, not left join
– tirth96
Mar 7 at 7:08
@tirth96 it will not work just by using join because with out left join you will loose the country from where still not ordered place
– Zaynul Abadin Tuhin
Mar 7 at 7:12
1
That LEFT JOIN returns regular INNER JOIN result. Move the orders.date conditions from WHERE to ON to get true LEFT JOIN result.
– jarlh
Mar 7 at 8:16
|
show 3 more comments
1
1
1
use modern join not coma separated join, do count for orders.id
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and orders.date <='2018-12-31'
group by country
You don't need parenthesis after group by
answered Mar 7 at 6:49
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.1k21033
use modern join not coma separated join, do count for orders.id
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and orders.date <='2018-12-31'
group by country
You don't need parenthesis after group by
answered Mar 7 at 6:49
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.1k21033
edited Mar 7 at 8:35
answered Mar 7 at 6:49
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.1k21033
answered Mar 7 at 6:49
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.1k21033
answered Mar 7 at 6:49
Zaynul Abadin TuhinZaynul Abadin Tuhin
16.1k21033
16.1k21033
I tried left and right join, still doesn’t work
– tirth96
Mar 7 at 6:57
@tirth96 create table here in fiddle dbfiddle.uk/… it should work if not then you need to help us by creating fiddle then we can solve your problem
– Zaynul Abadin Tuhin
Mar 7 at 6:58
ok nvm this worked, do you know it can be done with just join, not left join
– tirth96
Mar 7 at 7:08
@tirth96 it will not work just by using join because with out left join you will loose the country from where still not ordered place
– Zaynul Abadin Tuhin
Mar 7 at 7:12
1
That LEFT JOIN returns regular INNER JOIN result. Move the orders.date conditions from WHERE to ON to get true LEFT JOIN result.
– jarlh
Mar 7 at 8:16
|
show 3 more comments
I tried left and right join, still doesn’t work
– tirth96
Mar 7 at 6:57
@tirth96 create table here in fiddle dbfiddle.uk/… it should work if not then you need to help us by creating fiddle then we can solve your problem
– Zaynul Abadin Tuhin
Mar 7 at 6:58
ok nvm this worked, do you know it can be done with just join, not left join
– tirth96
Mar 7 at 7:08
@tirth96 it will not work just by using join because with out left join you will loose the country from where still not ordered place
– Zaynul Abadin Tuhin
Mar 7 at 7:12
1
That LEFT JOIN returns regular INNER JOIN result. Move the orders.date conditions from WHERE to ON to get true LEFT JOIN result.
– jarlh
Mar 7 at 8:16
I tried left and right join, still doesn’t work
– tirth96
Mar 7 at 6:57
I tried left and right join, still doesn’t work
– tirth96
Mar 7 at 6:57
@tirth96 create table here in fiddle dbfiddle.uk/… it should work if not then you need to help us by creating fiddle then we can solve your problem
– Zaynul Abadin Tuhin
Mar 7 at 6:58
@tirth96 create table here in fiddle dbfiddle.uk/… it should work if not then you need to help us by creating fiddle then we can solve your problem
– Zaynul Abadin Tuhin
Mar 7 at 6:58
ok nvm this worked, do you know it can be done with just join, not left join
– tirth96
Mar 7 at 7:08
ok nvm this worked, do you know it can be done with just join, not left join
– tirth96
Mar 7 at 7:08
@tirth96 it will not work just by using join because with out left join you will loose the country from where still not ordered place
– Zaynul Abadin Tuhin
Mar 7 at 7:12
@tirth96 it will not work just by using join because with out left join you will loose the country from where still not ordered place
– Zaynul Abadin Tuhin
Mar 7 at 7:12
1
1
That LEFT JOIN returns regular INNER JOIN result. Move the orders.date conditions from WHERE to ON to get true LEFT JOIN result.
– jarlh
Mar 7 at 8:16
That LEFT JOIN returns regular INNER JOIN result. Move the orders.date conditions from WHERE to ON to get true LEFT JOIN result.
– jarlh
Mar 7 at 8:16
|
show 3 more comments
2
use left join and move your where condition in ON clause
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by country
answered Mar 7 at 6:49
fa06fa06
16.2k21018
Tried that, still no luck
– tirth96
Mar 7 at 6:58
1
@tirth96: that should do exactly what you want. You need to edit and provide some sample data and the expected output on that data. Or create a short online example, e.g. here or here
– a_horse_with_no_name
Mar 7 at 7:07
ooh i see, this worked!
– tirth96
Mar 7 at 7:09
Do you know how I can do the same thing with just join?
– tirth96
Mar 7 at 7:09
@tirth96, you've to use left join - with inner join it's not possible
– fa06
Mar 7 at 7:10
add a comment |
2
use left join and move your where condition in ON clause
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by country
answered Mar 7 at 6:49
fa06fa06
16.2k21018
Tried that, still no luck
– tirth96
Mar 7 at 6:58
1
@tirth96: that should do exactly what you want. You need to edit and provide some sample data and the expected output on that data. Or create a short online example, e.g. here or here
– a_horse_with_no_name
Mar 7 at 7:07
ooh i see, this worked!
– tirth96
Mar 7 at 7:09
Do you know how I can do the same thing with just join?
– tirth96
Mar 7 at 7:09
@tirth96, you've to use left join - with inner join it's not possible
– fa06
Mar 7 at 7:10
add a comment |
2
2
2
use left join and move your where condition in ON clause
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by country
answered Mar 7 at 6:49
fa06fa06
16.2k21018
use left join and move your where condition in ON clause
select country, count(orders.id)
from customers left join orders
on customers.id = orders.id and orders.date >= '2018-01-01' and
orders.date <='2018-12-31'
group by country
answered Mar 7 at 6:49
fa06fa06
16.2k21018
answered Mar 7 at 6:49
fa06fa06
16.2k21018
answered Mar 7 at 6:49
fa06fa06
16.2k21018
answered Mar 7 at 6:49
fa06fa06
16.2k21018
16.2k21018
Tried that, still no luck
– tirth96
Mar 7 at 6:58
1
@tirth96: that should do exactly what you want. You need to edit and provide some sample data and the expected output on that data. Or create a short online example, e.g. here or here
– a_horse_with_no_name
Mar 7 at 7:07
ooh i see, this worked!
– tirth96
Mar 7 at 7:09
Do you know how I can do the same thing with just join?
– tirth96
Mar 7 at 7:09
@tirth96, you've to use left join - with inner join it's not possible
– fa06
Mar 7 at 7:10
add a comment |
Tried that, still no luck
– tirth96
Mar 7 at 6:58
1
@tirth96: that should do exactly what you want. You need to edit and provide some sample data and the expected output on that data. Or create a short online example, e.g. here or here
– a_horse_with_no_name
Mar 7 at 7:07
ooh i see, this worked!
– tirth96
Mar 7 at 7:09
Do you know how I can do the same thing with just join?
– tirth96
Mar 7 at 7:09
@tirth96, you've to use left join - with inner join it's not possible
– fa06
Mar 7 at 7:10
Tried that, still no luck
– tirth96
Mar 7 at 6:58
Tried that, still no luck
– tirth96
Mar 7 at 6:58
1
1
@tirth96: that should do exactly what you want. You need to edit and provide some sample data and the expected output on that data. Or create a short online example, e.g. here or here
– a_horse_with_no_name
Mar 7 at 7:07
@tirth96: that should do exactly what you want. You need to edit and provide some sample data and the expected output on that data. Or create a short online example, e.g. here or here
– a_horse_with_no_name
Mar 7 at 7:07
ooh i see, this worked!
– tirth96
Mar 7 at 7:09
ooh i see, this worked!
– tirth96
Mar 7 at 7:09
Do you know how I can do the same thing with just join?
– tirth96
Mar 7 at 7:09
Do you know how I can do the same thing with just join?
– tirth96
Mar 7 at 7:09
@tirth96, you've to use left join - with inner join it's not possible
– fa06
Mar 7 at 7:10
@tirth96, you've to use left join - with inner join it's not possible
– fa06
Mar 7 at 7:10
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%2f55037647%2fpostgresql-row-not-shown-when-count-is-zero%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
Tip of today: Always use modern, explicit
JOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Mar 7 at 7:35