Re: returning records with count grouping

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Tue, 15 Aug 2000 13:56:42 GMT
Message-ID: <k7iipsgso6mfi4bfm8oern4vcqcmo700ul@4ax.com>

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

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are

On Mon, 14 Aug 2000 20:56:44 GMT, mansoorzaidi_at_my-deja.com wrote:

>Now, I can group and try to get a total count by using count like this:
>
>"select count(*), firstname from blah where firstname = 'mansoor' group
>by firstname"
>
>but Oracle will reply with "no rows selected"
>
>Is there any way in which I can force Oracle to give back a record
>something along the lines of:
>
>COUNT(*) FIRSTNAME
>-------- ---------
> 0 mansoor
>
>Any help in this matter is appreciated. Thank you.
  Received on Tue Aug 15 2000 - 08:56:42 CDT