Multiple count(*) on same column Same query?

Hello,
Thank you for taking time to read my question.
I have somewhat a similar problem like this person in devarticles forum
- http://shorterlink.com/?NY7F6G
In my case, I have some cities, and every city will have Status column
with values 1/2/3/4 like this:
NYC 1 11/4/2005
NYC 3 11/2/2005
CHI 3 11/1/2005
CHI 4 11/4/2005
LA 1 11/20/2005
...
...
...
I'm trying to have a query that will have a result like this:
Status1Count Status2Count Status3Count Status4Count
NYC 100 80 20 40
LA 20 40 10 25
CHI 38 35 33 20
This next query gives the count for one status. I'm expecting to get
all the count for all the status like the above table:
SELECT a.citydesc, COUNT(a.status) as Status1Count
FROM a
WHERE a.Status=1
GROUP BY a.citydesc
Right now, in the existing code, they are hitting the database for
every cell in every row. I'm researching on how best to rewrite the
code to achieve this within one trip to the DB. Is it possible to
achieve this using just a single SELECT query ? Could you please give
me some pointers?
This is w.r.t SQL Server 2000
Thank you in Advance.See if this works (untested):
select citydesc,
SUM(case status when 1 then 1 else 0 end) as Status1Count,
SUM(case status when 2 then 1 else 0 end) as Status2Count,
SUM(case status when 3 then 1 else 0 end) as Status3Count,
SUM(case status when 4 then 1 else 0 end) as Status4Count
from a
group by citydesc
<dcmetro@.gmail.com> wrote in message
news:1132682010.500230.168440@.f14g2000cwb.googlegroups.com...
> Hello,
> Thank you for taking time to read my question.
> I have somewhat a similar problem like this person in devarticles forum
> - http://shorterlink.com/?NY7F6G
> In my case, I have some cities, and every city will have Status column
> with values 1/2/3/4 like this:
> NYC 1 11/4/2005
> NYC 3 11/2/2005
> CHI 3 11/1/2005
> CHI 4 11/4/2005
> LA 1 11/20/2005
> ...
> ...
> ...
> I'm trying to have a query that will have a result like this:
> Status1Count Status2Count Status3Count Status4Count
> NYC 100 80 20 40
> LA 20 40 10 25
> CHI 38 35 33 20
> This next query gives the count for one status. I'm expecting to get
> all the count for all the status like the above table:
> SELECT a.citydesc, COUNT(a.status) as Status1Count
> FROM a
> WHERE a.Status=1
> GROUP BY a.citydesc
> Right now, in the existing code, they are hitting the database for
> every cell in every row. I'm researching on how best to rewrite the
> code to achieve this within one trip to the DB. Is it possible to
> achieve this using just a single SELECT query ? Could you please give
> me some pointers?
> This is w.r.t SQL Server 2000
> Thank you in Advance.
>|||Hello Raymond,
Thank you very much for the hint. Yes, your suggestion works for me as
it is.
I got couple of questions when I was testing it. How do we rewrite the
query if the status is dynamic and is coming from another table? Is
there any way we could get the total of each StatusCount as a row in
the same query?
Thank you very much again.|||<dcmetro@.gmail.com> wrote in message
news:1132688082.041928.277300@.g43g2000cwa.googlegroups.com...
> Hello Raymond,
> Thank you very much for the hint. Yes, your suggestion works for me as
> it is.
> I got couple of questions when I was testing it. How do we rewrite the
> query if the status is dynamic and is coming from another table? Is
> there any way we could get the total of each StatusCount as a row in
> the same query?
> Thank you very much again.
>
Take a look at ROLLUP and see if this doesn't help you.
Rick Sawtell|||Nothing simple (unless someone else can jump in).
What I would do is this and handle presentation client side.
SELECT a.citydesc, a.status, COUNT(a.status) AS status
FROM a
GROUP BY a.citydesc, a.status
ORDER BY a.citydesc, a.status
Results:
CHI status1 38
CHI status2 35
...
LA status1 20
LA status2 40
LA status3 10
...
NYC...
If you're still looking for something else, post DDL (create table
statements) and insert statements for both tables.
<dcmetro@.gmail.com> wrote in message
news:1132688082.041928.277300@.g43g2000cwa.googlegroups.com...
> Hello Raymond,
> Thank you very much for the hint. Yes, your suggestion works for me as
> it is.
> I got couple of questions when I was testing it. How do we rewrite the
> query if the status is dynamic and is coming from another table? Is
> there any way we could get the total of each StatusCount as a row in
> the same query?
> Thank you very much again.
>|||Rick: Thanks for the pointer on ROLLUP. I was looking for that exact
functionality. Thanks again.
Raymond:
Here are the DDL for the two tables I'm talking about.
CREATE TABLE tblCities (
[CityID] [int] NULL ,
[CityDesc] [varchar] (20) NULL ,
[StatusID] [int] NULL,
[CreatedDate] [datetime] NULL )
CREATE TABLE [tblStatuses] (
[StatusID] [int] NOT NULL ,
[Description] [varchar] (25) NOT NULL ,
)
and some insert statements as well.
INSERT INTO [tblStatuses]([StatusID], [Description])
VALUES(1 , 'Status1 ' )
INSERT INTO [tblStatuses]([StatusID], [Description])
VALUES(2 , 'Status2 ' )
INSERT INTO [tblStatuses]([StatusID], [Description])
VALUES(3 , 'Status3 ' )