Thread: extend "group by" to include "empty relations" ?

I've two tables related via a id-field.


           Table "public.fn_kat"
     Column      |            Type             |
-----------------+-----------------------------+-
 id              | integer                     |
 kategorie       | text                        |


           Table "public.fn_dokumente"
     Column      |            Type             |
-----------------+-----------------------------+-
 kategorie       | integer                     |
 content         | text                        |


I now want to list all entries in table fn_kat and count the number of
entries in fn_dokumente that have the actual id.


# select k.kategorie,count(d.oid) from fn_kat k,fn_dokumente d where
k.id=d.kategorie group by k.kategorie;

                kategorie                 | count
------------------------------------------+-------
 1. Forschungsnetzwerk Erwachsenenbildung |     1
 1.1. Protokolle                          |     3
 2. Sonstige Dokumente                    |     1


But there is a problem now: There are also entries in fn_kat which dont
have corresponding entries in fn_dokumente and this entries should be
listed too. With the proper count=0 !!

How to achieve this?

thnx a lot,
peter






--
mag. peter pilsl - goldfisch.at
IT-Consulting
Tel: +43-650-3574035
Tel: +43-1-8900602
Fax: +43-1-8900602-15
skype: peter.pilsl
pilsl@goldfisch.at
www.goldfisch.at

peter pilsl wrote:

> But there is a problem now: There are also entries in fn_kat which dont
> have corresponding entries in fn_dokumente and this entries should be
> listed too. With the proper count=0 !!

Your problem is not on the GROUP BY, but rather that you need an outer
join.  Try something like

 select k.kategorie,count(d.oid) from fn_kat k left join fn_dokumente d
 on k.id=d.kategorie group by k.kategorie;

--
Alvaro Herrera                               http://www.PlanetPostgreSQL.org/
"The first of April is the day we remember what we are
the other 364 days of the year"  (Mark Twain)

peter pilsl <pilsl@goldfisch.at> writes:
> But there is a problem now: There are also entries in fn_kat which dont
> have corresponding entries in fn_dokumente and this entries should be
> listed too. With the proper count=0 !!

> How to achieve this?

LEFT JOIN before the group by?

            regards, tom lane