Count of all status in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count of all status in query

Author

Topic

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-04-01 : 19:31:55

Hi,
I've a table with following info:

Code VendorId CreatedOn Status
1234 3 12-Aug-2009 4
4567 4 18-Apr-2009 5
3589 3 14-Aug-2009 4
1234 3 17-Aug-2009 4
8941 3 13-Aug-2009 5
1234 3 15-Aug-2009 4

I need a query to find count of all different status for date >= 12-Aug-2009 but < 19-Aug-2009 for vendor Id = 3

Please note that Code is unique across vendor id.

In this case, output should be as shown:

Count Status
2 4
1 5

Can any one provide me query?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-01 : 19:35:13

[code]
select count(distinct VendorID), Status
from atable
where VendorID = 3
and CreateOn >= '20090812'
and CreateOn < '20090819'
group by Status
[/code]

KH
[spoiler]Time is always against us[/spoiler]

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-04-01 : 19:40:37

Hi khtan,
Thanks for the super quick reply!!!!

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-02 : 03:07:43

quote:
Originally posted by khtan

select count(distinct VendorID), Status
from atable
where VendorID = 3
and CreateOn >= '20090812'
and CreateOn < '20090819'
group by Status

KH
[spoiler]Time is always against us[/spoiler]

shouldnt it be


select count(distinct Code), Status
from   atable
where  VendorID = 3
and    CreateOn >= '20090812'
and    CreateOn <  '20090819'
group by Status

as per OP's output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-02 : 03:17:11

you are right. Thanks for the correction