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.
| Author |
Topic |
|
Serge
Starting Member
44 Posts |
 Posted - 2008-03-12 : 07:07:06
|
Hi,I have a table resulting from a query that looks similar to this:Name | Value1 | Value2 | Value3 | Value4 Bob 3 1 0 0 Steve 0 0 3 3 Jack 1 1 0 0 Jack 5 2 0 0 Peter 0 0 1 1 John 4 1 0 0etc... I do grouping of items on Name value so Jack appear once and the Value1 shows 6 and Value2 shows 3. So the user sees it like this:Name | Value1 | Value2 | Value3 | Value4 Bob 3 1 0 0 Steve 0 0 3 3 Jack 6 3 0 0 Peter 0 0 1 1 John 4 1 0 0etc... Now I need to get a distinct count of records that have zero in Value4 and Value2 fields. So I get count result for Value4 equals to 3 and for Value2 I get 2. However there might be some records which have a zeros in both fields but I don't care about those.Many thanks |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-03-12 : 07:13:26
|
| [code]Select [Name],Sum(case when Value2 = 0 then 1 else 0 end) as Value2Count,Sum(case when Value4 = 0 then 1 else 0 end) as Value4CountFrom TableGroup by [Name][/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Serge
Starting Member
44 Posts |
Posted - 2008-03-12 : 07:40:32
|
| Thanks a lot!Is there are a way to achieve the same result but using inbuilt SSRS functions rather than connecting to the Database |
|
|
|
|
|
|
Count Distinct Values