Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: returning records with count grouping
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)
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)
SUM(NO) FORENAME
---------- ---------------------------------------- 75 ROBERT
1 row selected.
Hth,
MF
-- matt_foster_uk_at_yahoo.co.ukReceived on Tue Aug 15 2000 - 11:02:42 CDT