Retrieving Counts and Total on a column

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

Retrieving Counts and Total on a column

Author

Topic

mclovin
Starting Member

15 Posts

Posted - 2011-04-25 : 11:13:23

Hello I would Like to get the total count of a field that has different status. What I have is a column called Status and in this column it has data such as 'approved', return, declined. what i would like to do is be able to count each data and then sum there total. any Help would be appreciated thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 11:16:21

SELECT Status,count(*)
FROM yourTable
GROUP BY Status

Jim

Everyday I learn something that somebody else already knew

mclovin
Starting Member

15 Posts

Posted - 2011-04-25 : 13:14:05

Thanks Jimf for responding, but just a question is it possible to separate the Status in their own columns instead of all them under one column. Thanks again

Sachin.Nand

2937 Posts

Posted - 2011-04-25 : 13:42:51

Something like this ?

...sum(case when status='Yes' then 1 else 0 end)YesStatus,
sum(case when status='No' then 1 else 0 end)NoStatus

PBUH

mclovin
Starting Member

15 Posts

Posted - 2011-04-25 : 14:38:07

Thanks Sachin for responding, i'm sorry if I’m making this more complicated then it probably is but what I’m looking for is each result Status to come back in its own column. Ex and this is only an example i know in SQL this would not make sense but this is what I’m kind of looking for.

Select Sum(amount) as TotalAmountApproved, Count(*) as TotalApproved From THANK where status = 'Approved'
Select Sum(amount) as TotalAmountReturned, Count(*) as TotalReturned
From THANK where status = 'Returned'
Select Sum(amount) as TotalDeclined, Count(*) as TotalDeclined
From THANK where status = 'Declined'

I'm looking for this but in a sql statement not three different ones, the reason for that is I’m programming in C# and i need the columns values to use as variables in my code. Thanks again and maybe what I’m asking for cannot be done or could in another way. Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 16:20:28

Use sachin's example

SELECT SUM(CASE WHEN [status] = 'Approved' THEN 1 ELSE 0 END) as TotalApproved
,SUM(CASE WHEN [status] = 'returned' THEN 1 ELSE 0 END) as Totalreturned
,etc.

Jim

Everyday I learn something that somebody else already knew

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-04-25 : 19:14:27

It looks like you want both the sum and the count - so, adding to sachin's and jimf's solution:

SELECT SUM(CASE WHEN [status] = 'Approved' THEN amount ELSE 0 END) As TotalAmountApproved
,SUM(CASE WHEN [status] = 'Approved' THEN 1 ELSE 0 END) As TotalApproved
,{repeat the above for each status}
FROM yourTable
WHERE {your conditions}

Jeff

mclovin
Starting Member

15 Posts

Posted - 2011-04-25 : 19:29:16

Thank a lot guys will give that a try. thanks again for responding really appreciate it

mclovin
Starting Member

15 Posts

Posted - 2011-04-26 : 09:09:35

Thanks A lot Guys it work like a charm, really appreciate all the responses. Once again thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources