Thread: showing also value '0' with aggregate count()
I need some help please,
i am having two simple tables:
# select * from sales;
client_id | product
-----------+---------
1 | toolbox
1 | nails
2 | nuts
# select * from clients;
id | name
----+-------
1 | peter
2 | john
3 | marc
now i want to show all client name and the count of the sales in one
table like this:
# select name, count(sales.client_id) from clients, sales where
sales.client_id = clients.id group by name;
name | count
-------+-------
john | 1
peter | 2
works fine, but where is marc??? it should look like
name | count
-------+-------
john | 1
peter | 2
marc | 0
who can i make it work??? i think i know why my select statement
doesnt work, because of the where clause marc will never join this
table because the condition will never be true... but how can i do
it??
i guess its very very simple, but i just cant manage it.
thanks in advance
janning
Try a left join: select name, count(client_id) AS cnt from clients left join sales on (client_id = id) group by name order by cnt; There's a great book for SQL called "The Practical SQL Handbook" which covers these types of problems well. Published by Addison Wesley, written by Bowman, Emerson, and Darnovsky. Good bedtime reading. :) HTH, David On 9/27/01 10:51 AM, "Janning Vygen" <vygen@planwerk6.de> wrote: > I need some help please, > > i am having two simple tables: > > # select * from sales; > client_id | product > -----------+--------- > 1 | toolbox > 1 | nails > 2 | nuts > > # select * from clients; > id | name > ----+------- > 1 | peter > 2 | john > 3 | marc > > now i want to show all client name and the count of the sales in one > table like this: > > # select name, count(sales.client_id) from clients, sales where > sales.client_id = clients.id group by name; > > name | count > -------+------- > john | 1 > peter | 2 > > works fine, but where is marc??? it should look like > > name | count > -------+------- > john | 1 > peter | 2 > marc | 0 > > who can i make it work??? i think i know why my select statement > doesnt work, because of the where clause marc will never join this > table because the condition will never be true... but how can i do > it?? > > i guess its very very simple, but i just cant manage it. > > thanks in advance > janning > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Am Donnerstag, 27. September 2001 17:18 schrieb David Christian: > Try a left join: > > select name, count(client_id) AS cnt from clients left join sales > on (client_id = id) group by name order by cnt; # select name, count(client_id) AS cnt from clients left join sales on (client_id = id) group by name order by cnt; ERROR: OUTER JOIN is not yet supported (PostgreSQL) 7.0.3 do i need to upgrade or is there a workaround?? > There's a great book for SQL called "The Practical SQL Handbook" > which covers these types of problems well. Published by Addison > Wesley, written by Bowman, Emerson, and Darnovsky. Good bedtime > reading. :) can you read my mind?? I am looking for a really good book on SQL. i will buy this and we'll see how tired i will be tomorrow.. thanks janning > HTH, > David > > On 9/27/01 10:51 AM, "Janning Vygen" <vygen@planwerk6.de> wrote: > > I need some help please, > > > > i am having two simple tables: > > > > # select * from sales; > > client_id | product > > -----------+--------- > > 1 | toolbox > > 1 | nails > > 2 | nuts > > > > # select * from clients; > > id | name > > ----+------- > > 1 | peter > > 2 | john > > 3 | marc > > > > now i want to show all client name and the count of the sales in > > one table like this: > > > > # select name, count(sales.client_id) from clients, sales where > > sales.client_id = clients.id group by name; > > > > name | count > > -------+------- > > john | 1 > > peter | 2 > > > > works fine, but where is marc??? it should look like > > > > name | count > > -------+------- > > john | 1 > > peter | 2 > > marc | 0 > > > > who can i make it work??? i think i know why my select statement > > doesnt work, because of the where clause marc will never join > > this table because the condition will never be true... but how > > can i do it?? > > > > i guess its very very simple, but i just cant manage it. > > > > thanks in advance > > janning > > > > ---------------------------(end of > > broadcast)--------------------------- TIP 3: if posting/reading > > through Usenet, please send an appropriate subscribe-nomail > > command to majordomo@postgresql.org so that your message can get > > through to the mailing list cleanly -- Planwerk 6 /websolutions Herzogstra�e 86 40215 D�sseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
Am Donnerstag, 27. September 2001 17:47 schrieb David Christian: > Hmmm... I'm using 7.1.2, and don't know if you should upgrade or > not. > > Can you use UNIONs? > > select name, count(client_id) from sales, clients where client_id = > id group by name UNION select name,0 from clients where not id in > (select client_id from sales) order by 2; Oh yes! Thanks. i dont need to upgrade. thats fine. thanks to randal too. janning -- Planwerk 6 /websolutions Herzogstra�e 86 40215 D�sseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
Janning Vygen <vygen@planwerk6.de> writes: > ERROR: OUTER JOIN is not yet supported > (PostgreSQL) 7.0.3 > > do i need to upgrade or is there a workaround?? 7.1 introduced support for outer joins. -Doug -- In a world of steel-eyed death, and men who are fighting to be warm, Come in, she said, I'll give you shelter from the storm. -Dylan
Hmmm... I'm using 7.1.2, and don't know if you should upgrade or not. Can you use UNIONs? select name, count(client_id) from sales, clients where client_id = id group by name UNION select name,0 from clients where not id in (select client_id from sales) order by 2; Yes, when I said "Try a left join" I should have said "left outer join"...the "OUTER" is commonly omitted. Thanks. :) Note that with UNION, you refer to columns by number, hence "order by 2". David On 9/27/01 11:35 AM, "Janning Vygen" <vygen@planwerk6.de> wrote: > Am Donnerstag, 27. September 2001 17:18 schrieb David Christian: >> Try a left join: >> >> select name, count(client_id) AS cnt from clients left join sales >> on (client_id = id) group by name order by cnt; > > # select name, count(client_id) AS cnt from clients left join sales on > (client_id = id) group by name order by cnt; > > ERROR: OUTER JOIN is not yet supported > (PostgreSQL) 7.0.3 > > do i need to upgrade or is there a workaround?? > >> There's a great book for SQL called "The Practical SQL Handbook" >> which covers these types of problems well. Published by Addison >> Wesley, written by Bowman, Emerson, and Darnovsky. Good bedtime >> reading. :) > > can you read my mind?? I am looking for a really good book on SQL. i > will buy this and we'll see how tired i will be tomorrow.. > > thanks > janning > >> HTH, >> David >> >> On 9/27/01 10:51 AM, "Janning Vygen" <vygen@planwerk6.de> wrote: >>> I need some help please, >>> >>> i am having two simple tables: >>> >>> # select * from sales; >>> client_id | product >>> -----------+--------- >>> 1 | toolbox >>> 1 | nails >>> 2 | nuts >>> >>> # select * from clients; >>> id | name >>> ----+------- >>> 1 | peter >>> 2 | john >>> 3 | marc >>> >>> now i want to show all client name and the count of the sales in >>> one table like this: >>> >>> # select name, count(sales.client_id) from clients, sales where >>> sales.client_id = clients.id group by name; >>> >>> name | count >>> -------+------- >>> john | 1 >>> peter | 2 >>> >>> works fine, but where is marc??? it should look like >>> >>> name | count >>> -------+------- >>> john | 1 >>> peter | 2 >>> marc | 0 >>> >>> who can i make it work??? i think i know why my select statement >>> doesnt work, because of the where clause marc will never join >>> this table because the condition will never be true... but how >>> can i do it?? >>> >>> i guess its very very simple, but i just cant manage it. >>> >>> thanks in advance >>> janning >>> >>> ---------------------------(end of >>> broadcast)--------------------------- TIP 3: if posting/reading >>> through Usenet, please send an appropriate subscribe-nomail >>> command to majordomo@postgresql.org so that your message can get >>> through to the mailing list cleanly
Re: showing also value '0' with aggregate count()
From
merlyn@stonehenge.com (Randal L. Schwartz)
Date:
>>>>> "Janning" == Janning Vygen <vygen@planwerk6.de> writes: Janning> I need some help please, Janning> i am having two simple tables: Janning> # select * from sales; Janning> client_id | product Janning> -----------+--------- Janning> 1 | toolbox Janning> 1 | nails Janning> 2 | nuts Janning> # select * from clients; Janning> id | name Janning> ----+------- Janning> 1 | peter Janning> 2 | john Janning> 3 | marc Janning> now i want to show all client name and the count of the sales in one Janning> table like this: you need a left join... merlyntest=# select name, count(sales.client_id) merlyntest-# from clients left join sales on clients.id = sales.client_id merlyntest-# group by name; name | count -------+------- john | 1 marc | 0 peter | 2 (3 rows) -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
> ERROR: OUTER JOIN is not yet supported > (PostgreSQL) 7.0.3 > > do i need to upgrade or is there a workaround?? Upgrade. :-) > > There's a great book for SQL called "The Practical SQL Handbook" > > which covers these types of problems well. Published by Addison > > Wesley, written by Bowman, Emerson, and Darnovsky. Good bedtime > > reading. :) > > can you read my mind?? I am looking for a really good book on SQL. i > will buy this and we'll see how tired i will be tomorrow.. Bruce's book is a good PostgreSQL book -- http://www.ca.postgresql.org/docs/awbook.html -Mitch
You need a left outer join:
select name, count(sales.client_id) from clients left outer join sales on
sales.client_id = clients.id group by name;
Outer joins were not supported prior to 7.1, so if you're using an earlier
version, you'll want to either upgrade or read the docs for your version; they
had a standard workaround prior to 7.1.
Wes Sheldahl
Janning Vygen <vygen%planwerk6.de@interlock.lexmark.com> on 09/27/2001 10:51:45
AM
To: "PostgreSQL-General" <pgsql-general%postgresql.org@interlock.lexmark.com>
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] showing also value '0' with aggregate count()
I need some help please,
i am having two simple tables:
# select * from sales;
client_id | product
-----------+---------
1 | toolbox
1 | nails
2 | nuts
# select * from clients;
id | name
----+-------
1 | peter
2 | john
3 | marc
now i want to show all client name and the count of the sales in one
table like this:
# select name, count(sales.client_id) from clients, sales where
sales.client_id = clients.id group by name;
name | count
-------+-------
john | 1
peter | 2
works fine, but where is marc??? it should look like
name | count
-------+-------
john | 1
peter | 2
marc | 0
who can i make it work??? i think i know why my select statement
doesnt work, because of the where clause marc will never join this
table because the condition will never be true... but how can i do
it??
i guess its very very simple, but i just cant manage it.
thanks in advance
janning
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Am Donnerstag, 27. September 2001 19:01 schrieb David Christian:
> > btw: intersting point is, that the solution with union doesnt
> > work when client_id might be null.
> >
> > dont knwo why ...
>
> In this case, use "select all". Though, I advise against null
> fields in this table. Something like this would be better:
>
> create table sales (client_id integer default 0 not null, product
> text);
>
> That way you always have a "known" value. The value 0 (or whatever
> else you choose) means there is no client id associated with the
> product (or sale or whatever it is you are doing).
ok this is a good hint!
> Anyway, here's the statement that will work in your case:
>
> select name, count(client_id) from sales, clients where client_id =
> id group by name UNION select name,0 from clients where not id in
> (select all client_id from sales) order by 2;
'ALL' is the default, so it doesn't change anything.
it still dont work with NULL values.
here are my tables with a NULL value in the client_id field.
# select * from sales;
client_id | product
-----------+----------
1 | toolbox
1 | nails
2 | nuts
| junkfood
# select client_id from sales;
client_id
-----------
1
1
2
# select * from clients;
id | name
----+-------
1 | peter
2 | john
3 | marc
# select name,0 from clients where not id in (select all client_id
from sales) order by 2;
name | ?column?
------+----------
(0 rows)
THE FOLLOWING STATEMENT WORKS! WHY????
#select name,0 from clients where not id in (select all client_id
from sales where client_id is not null) order by 2;
name | ?column?
------+----------
marc | 0
(1 row)
Whats the difference?? when i ask for rows with "not id in" and my id
is 3 and the subquery returns a NULL but no '3' than '3 is in NULL'
???
Look at this:
select 'funny' where not 3 in (NULL);
?column?
----------
(0 rows)
3 is not in NULL!!
testdb=# select 'funny' where not 3 in ( 2, 1);
?column?
----------
funny
(1 row)
Janning
> Whats the difference?? when i ask for rows with "not id in" and my id > is 3 and the subquery returns a NULL but no '3' than '3 is in NULL' > ??? > > Look at this: > > select 'funny' where not 3 in (NULL); > ?column? > ---------- > (0 rows) > > 3 is not in NULL!! Actually, you don't know that. That's part of the three valued logic and unknown NULL. Since NULL is an unknown value, you don't know whether a 3 is equal to that NULL or not. -- Stupid NULL related logic stuff -- The series goes like this A NOT IN B is the same as NOT (A IN B) is the same as NOT (A =ANY B) The result of A =ANY B is derived by the application of the implied comparison predicate A = BT to every row in B [in this case the NULL is effectively a one row values list]. If the = is true for at least one row BT in B, then A=ANY B is true. If B is empty or if the = is false for every row BT in B then A=ANY B is false. Otherwise A=ANY B is unknown. A=B is unknown if either A or B is the null value. Otherwise A=B is true iff X and Y are equal. [simplified version of the equality rules] So, it does the 3=NULL comparison and gets an unknown back. There are no other rows, so it was not true for at least one row, nor was it false for all rows, so the =ANY is unknown. NOT (unknown) is unknown. And the where clause returns those rows where the condition is true so the row is not returned.
> ERROR: OUTER JOIN is not yet supported > (PostgreSQL) 7.0.3 > > do i need to upgrade or is there a workaround?? Upgrade. :-) > > There's a great book for SQL called "The Practical SQL Handbook" > > which covers these types of problems well. Published by Addison > > Wesley, written by Bowman, Emerson, and Darnovsky. Good bedtime > > reading. :) > > can you read my mind?? I am looking for a really good book on SQL. i > will buy this and we'll see how tired i will be tomorrow.. Bruce's book is a good PostgreSQL book -- http://www.ca.postgresql.org/docs/awbook.html -Mitch