Multiple COUNTs with a WHERE clause? 1

[biffer711]

I'm trying to create a query with multiple counts on a subset of a table.

I've found:

SELECT (SELECT COUNT(*)FROM Table WHERE Item = 1) AS Field1,
(SELECT COUNT(*)FROM Table WHERE Item = 2) AS Field2


which does return the total count of Items = 1 and Items = 2 in Table. My problem is that I'd like to perform these counts on a subset of the table, for example 'WHERE Client = 1' and I'd also like to return the client number.

Anyone with any ideas?

Thanks,

Biff

 

[tlbroadbent]

Using Group By is the easiest way to get the result you want though it will come in multiple rows rather than 1.

SELECT Item, RowCnt=Count(*)
FROM Table
GROUP BY Item Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[biffer711]

Thanks, Terry!

Not as pretty as I'd like, but it gives me what I need.

 

[crystalized]

I realize you indicated a working solution but I will mention something I have used before for what I think is a similar situation.

Select client,
Sum(Case When Item=1 then 1 else 0 End) as Field1,
Sum(Case When Item=2 then 1 else 0 End) as Field2
From Table
Where client=1

This should return something like
Client Field1 Field2
1 3 2

Or if you want to have the results for each of the clients together in the result set you would remove the where clause and put in

Group by Client

The group by one would return something like
Client Field1 Field2
1 3 2
2 1 7
3 5 4

Where the field 1 and field 2 values are those calculated in the query.

Hope this helps
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[biffer711]

Thanks Crystallized! Exactly what I was looking for!

Thanks a lot,

Biff