showing also value '0' with aggregate count()

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:

"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/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

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 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

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:

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

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

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.