Counts Useing Group By

[Matthew Ulmer]

Hello All,

I would like to have these two queries returned in one query or recordset
(essentially getting a count of the Y and another count of the N in which
both are located in the same field and grouped by vcStoreNo):
-----
Select vcStoreNo, Count(cOutsideLotClean) as No From tblScore Where cOutsideLotClean
= 'N' Group By vcStoreNo

Select vcStoreNo, Count(cOutsideLotClean) as Yes From tblScore Where cOutsideLotClean
= 'Y' Group By vcStoreNo
-----

I have tried useing :
-----
Select vcStoreNo,
(
Select Count(cOutsideLotClean) From tblScore
Where cOutsideLotClean = 'Y'
) As Yes,
(
Select Count(cOutsideLotClean) From tblScore
Where cOutsideLotClean = 'N'
) As No
From tblScore Group By vcStoreNo
-----

But that returns the count of all the yes's in the table and you can not
add a group by clause to a sub query or it will rerun an error.

Help !!!

Matt Ulmer

[dnagel]

what about this:


Select
vcStoreNo,
Count(cOutsideLotClean),
decode(cOutsideLotClean,'Y','Yes,'N','No') LotCleanYN
From
tblScore
Group By
vcStoreNo,
cOutsideLotClean;

D.


"Matthew Ulmer" <matt@ulmers.net> wrote in message
news:3b336147$1@news.devx.com...
>
> Hello All,
>
> I would like to have these two queries returned in one query or recordset
> (essentially getting a count of the Y and another count of the N in which
> both are located in the same field and grouped by vcStoreNo):
> -----
> Select vcStoreNo, Count(cOutsideLotClean) as No From tblScore Where
cOutsideLotClean
> = 'N' Group By vcStoreNo
>
> Select vcStoreNo, Count(cOutsideLotClean) as Yes From tblScore Where
cOutsideLotClean
> = 'Y' Group By vcStoreNo
> -----
>
> I have tried useing :
> -----
> Select vcStoreNo,
> (
> Select Count(cOutsideLotClean) From tblScore
> Where cOutsideLotClean = 'Y'
> ) As Yes,
> (
> Select Count(cOutsideLotClean) From tblScore
> Where cOutsideLotClean = 'N'
> ) As No
> From tblScore Group By vcStoreNo
> -----
>
> But that returns the count of all the yes's in the table and you can not
> add a group by clause to a sub query or it will rerun an error.
>
> Help !!!
>
> Matt Ulmer