Re: returning records with count grouping

From: Matt Foster <matt_foster_uk_at_yahoo.co.uk>
Date: Tue, 15 Aug 2000 17:02:42 +0100
Message-ID: <399969A2.773842D0@yahoo.co.uk>

Jonathan Gennick wrote:
>
> I don't know any way to force a count to come back when rows
> do not exist, but you might try an approach like this:
>
> SELECT 1,COUNT(*),firstname
> FROM blah
> WHERE firstname='mansoor'
> GROUP BY firstname
> UNION ALL
> SELECT 2,0,'mansoor'
> FROM dual
> ORDER BY 1;
>
> You'll get two rows back if there are names with 'mansoor',
> and one row back otherwise. The real count will always be
> first, and the artificial zero count will be second. Open a
> cursor for the SELECT, and fetch only the first row.
>
> Jonathan
>

You could take this approach a step further and return only one line with genuine figure

SELECT SUM(no), firstname
FROM (SELECT 1,COUNT(*) AS no,firstname

      FROM persons
      WHERE firstname='mansoor'
      GROUP BY firstname
      UNION ALL
      SELECT 2,0,'mansoor'
      FROM dual
      ORDER BY 1)

GROUP BY firstname;

   SUM(NO) FORENAME

---------- ----------------------------------------
         0 mansoor

1 row selected.

SELECT SUM(no), firstname
FROM (SELECT 1,COUNT(*) AS no,firstname

      FROM persons
      WHERE firstname='ROBERT'
      GROUP BY firstname
      UNION ALL
      SELECT 2,0,'ROBERT'
      FROM dual
      ORDER BY 1)

GROUP BY firstname;

   SUM(NO) FORENAME

---------- ----------------------------------------
        75 ROBERT

1 row selected.

Hth,
MF

-- 
matt_foster_uk_at_yahoo.co.uk

Received on Tue Aug 15 2000 - 11:02:42 CDT