"Lisa Calla" <spamstopper@herplace.com> wrote in message
news:de6dnfvV3vMvcEPeRVn-uA@eatel.net...
> Hi,
> I have a table with productIDs, productShapes, and productColors columns.
> I'd like a query that results in:
>
> productShape A Count(products regardless of color) Count(Blue
> products)
> productShape B Count(products regardless of color) Count(Blue
> products)
> productShape ... Count(products regardless of color) Count(Blue
> products)
>
> Is it necessary to use a nested select statement? My sql experience has
> been somewhat limited, though now I've upgraded to SQL Server 2005
> Express. TIA for any help.
>
>
>

On Mon, 30 Jan 2006 22:55:04 -0600, Lisa Calla wrote:

>Hi,
>I have a table with productIDs, productShapes, and productColors columns.
>I'd like a query that results in:
>
>productShape A Count(products regardless of color) Count(Blue
>products)
>productShape B Count(products regardless of color) Count(Blue
>products)
>productShape ... Count(products regardless of color) Count(Blue
>products)
>
>Is it necessary to use a nested select statement?

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:oqqvt1lr8naasf2jumbaaomjj1u8icf2jv@4ax.com...
> On Mon, 30 Jan 2006 22:55:04 -0600, Lisa Calla wrote:
>
>>Hi,
>>I have a table with productIDs, productShapes, and productColors columns.
>>I'd like a query that results in:
>>
>>productShape A Count(products regardless of color) Count(Blue
>>products)
>>productShape B Count(products regardless of color) Count(Blue
>>products)
>>productShape ... Count(products regardless of color) Count(Blue
>>products)
>>
>>Is it necessary to use a nested select statement?
>
> Hi Lisa,
>
> Not at all. In fact, it's far more efficient to use a simple query with
> a CASE expression:
>
> SELECT ProductShape,
> COUNT(*) AS CountOfAllColours,
> COUNT(CASE WHEN ProductColor = 'Blue'
> THEN 'Count Me'
> END) AS CountOfOnlyBlueProducts
> FROM YourTable
> GROUP BY ProductShape
>
> (untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>
> --
> Hugo Kornelis, SQL Server MVP