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

From: peter pilsl <pilsl(at)goldfisch(dot)at>
To: pgsql-general(at)postgresql(dot)org
Subject: extend "group by" to include "empty relations" ?
Date: 2007-12-13 18:42:00
Message-ID: 47617CF8.7010907@goldfisch.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)goldfisch(dot)at
www.goldfisch.at

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-12-13 18:56:02 Re: COPY speedup
Previous Message robert 2007-12-13 18:38:36 Finding bad bye in "invalid byte sequence" error