pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott A. Barron <kain@dynup.net>
To: pgsql-sql@postgresql.org
Subject: Showing rows where COUNT() is 0
Date: Fri, 27 Aug 1999 04:15:12 -0400 (EDT)
Message-ID: <XFMail.990827041512.kain@dynup.net> (raw)

Hello all,

Given the following tables:

T1          T2                T3
-------------------------------------------
usid | c     sid | usid | c     sid | usid | r

where uid, sid, and r are int's, and c is text

I have the query:

SELECT t1.c, COUNT(t2.usid), AVG(t3.r)
    FROM t1, t2, t3
    WHERE t2.usid = t1.usid AND t3.sid = t2.sid
    GROUP BY t1.c

Which gives me the total number of sid's in t2 belonging to t1.usid and the
average of t3.r for each t2.sid belonging to each t1.usid.

The query works fine exept it doesn't show output where COUNT() returns 0.  I
would like to list all of the results even if there is nothing in t2 to match
the usid in t1.  Is there a way I can make this happen?

Thanks,
Scott



Message-ID: <XFMail.990827041512.kain@dynup.net>
Permalink:  ../XFMail.990827041512.kain@dynup.net/
Also on:    postgresql.org/message-id/XFMail.990827041512.kain@dynup.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: kain@dynup.net
  Subject: Re: Showing rows where COUNT() is 0
  In-Reply-To: <XFMail.990827041512.kain@dynup.net>
  References: <XFMail.990827041512.kain@dynup.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox