Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I return the total count of a column through 3 filters?

Posted on 2011-03-16
15
Medium Priority
?
331 Views
Last Modified: 2012-05-11
I need a query that will return a single summary row on a single column for three different filters so that the output looks like this:

--------Filter1--------Filter2--------Filter3
Total----30-------------52------------22  
Count
0
Comment
Question by:rhservan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35153370

just assign the results to variables and return the variables

declare @count1 integer,  @count2 integer, @count3 integer

select @count1 = count(field1) from table1 where filter1
select @count2 = count(field2) from table1 where filter2
select @count3 = count(field3) from table1 where filter3

--your result
select 'Filter1', 'Filter2', 'Filter3'
union all
select cast(@count1 as varchar), cast(@count2 as varchar), cast(@count3 as varchar)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35153570
use a case statement for the filters


select count(case when filter1  then 1 else null end) as fil1
    ,count(case when filter2 then 1 else null end) as fil2
    ,count(case when filter3 then 1 else null end) as fil3
from yourtable
where ....



 
0
 

Expert Comment

by:nirmalrampk
ID: 35154111
Removing null values when you are performing calculations can be important, because certain calculations, such as an average, can be inaccurate if NULL columns are included (ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/f77b3bfe-b5be-417e-96a3-ff3a19c33119.htm).

    So better to avoid ‘NULL’  
select count(case when filter1  then 1 else null end) as fil1
    ,count(case when filter2 then 1 else null end) as fil2
    ,count(case when filter3 then 1 else null end) as fil3
from yourtable
where ....


Instead you can try below one

SELECT      SUM(CASE WHEN Filter1 THEN 1 ELSE 0 END) FilterData1,
                     SUM(CASE WHEN Filter2  THEN 1 ELSE 0 END) FilterData2,
      SUM(CASE WHEN Filter3 THEN 1 ELSE 0 END) FilterData3
FROM MyTable
WHERE <FilterCondition>


E.g.
-----------
SELECT      SUM(CASE Name = 'Name1' THEN 1 ELSE 0 END) FilterData1,
                     SUM(CASE Age < 20 THEN 1 ELSE 0 END) FilterData2,
      SUM(CASE Gender = 'M' THEN 1 ELSE 0 END) FilterData3
FROM MyTable
WHERE Country = 'XYZ'
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35157742
#Null# in averages etc...

it all depends on what you are trying to achieve... and wether or not the filters are related or independant  as to wether to use NULL or 0 with the aggregate functions...

0
 

Author Comment

by:rhservan
ID: 35160345
@lowfatspread & nirmalrampk
My concern right now is the sums are not correct yet.  It returns a much higher number in some cases than what is actually the count.      What I percieve is going on is the sum\counts are filtered correctly but the returns are like this:
There is a column in the table which adds more rows, for an example:
ColumnA------------ColumnB
Type1----------------Type1a
Type1----------------Type1b
Type1----------------Type1c
Type1----------------Type1d
Type2----------------Type2a
Type2----------------Type2b
etc.........
Now the count needs to be on the distinct single count of each type in ColumnA.  In the example there are 2 Types in ColumnA.  The query results we are discussing retruns ColumnB for a total count of 6 as in the example.
Here is what I need:
I need a count of 2 as in ColumnA and I am getting a count of 6 as in ColumnB. How do Ido this?



0
 

Author Comment

by:rhservan
ID: 35160565
@ewangoya  I was able to make it work as you wrote it above. However, Once I add these two params to each filter, which I need to exclude other items,  My return is all zeroes.

AND DimCompany.CompanyDesc IN (@CompanyName)
AND(AdHoc.DimDate.Date_AK BETWEEN @StartDate AND @EndDate)

I copied the below attachment to my SSRS Dataset and I can no longer make it work with these.  Any ideas?
SampleEwangoya.txt
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35160639
AND DimCompany.CompanyDesc IN (@CompanyName)

doesn't make any sense... you can only test against a single value....
EQUALS or NOT EQUALS....

if @companyname is supposed to be a list of values then you need to parse that through a table valued function in order for IN processing to work...
0
 

Author Comment

by:rhservan
ID: 35161194
@lowfatspread - If you are referring to the attachment based ewangoya, Ill check on that.  But What about  35160345 referring to your original query any thoughts?
0
 

Author Comment

by:rhservan
ID: 35161391
Also,  there are no nulls.
0
 

Author Comment

by:rhservan
ID: 35164993
More informatiopn, COUNT returns all rows even if distinct is used.  
0
 

Author Comment

by:rhservan
ID: 35166190
Okay,  Nothing has worked up to this point so it is still anybodys game.  Here is another way to approach it: The query below gives me the exact output I am looking for except it is single column & single row. Put this exact query into 6 columns, 1 row.   How can this be done?!
SELECT     COUNT(DISTINCT DimInvoiceDetail.Invoice_AK) AS 'Inv #'
FROM         AdHoc.DimInvoiceDetail AS DimInvoiceDetail INNER JOIN AdHoc.DimCustomer AS DimCustomer ON DimInvoiceDetail.Customer_SK = DimCustomer.Customer_SK INNER JOIN
AdHoc.DimCompany AS DimCompany ON DimInvoiceDetail.Company_SK = DimCompany.Company_SK INNER JOIN AdHoc.SalesData AS SalesData ON DimInvoiceDetail.InvoiceDetail_SK = SalesData.InvoiceDetail_SK INNER JOIN AdHoc.DimDate ON DimInvoiceDetail.InvoiceDate_SK = AdHoc.DimDate.Date_SK
WHERE     (DimInvoiceDetail.OrderType NOT IN ('DE', 'DH', 'DJ', 'DK', 'DL', 'DM', 'E2', 'E4', 'W2', 'W4', 'W5', 'W6')) AND (SalesData.GPPercentage <= 0.05) AND (AdHoc.DimDate.Date_AK BETWEEN @StartDate AND @EndDate) AND (DimCompany.CompanyDesc IN (@CompanyName))
0
 

Author Comment

by:rhservan
ID: 35166281
There has been no solution to this prolem in responses above.
Please disregard this question and respond to new submitted question  regarding same problem
Thanks for your expert help
0
 

Author Comment

by:rhservan
ID: 35184746
This solution was derived based on erwangoya:
Thanks E:

Countwith6col-1row.txt
0
 

Author Comment

by:rhservan
ID: 35189294
I found that one of the experts above have provided the correct information and would like to award points.  I apologize for the way things went here.  But, I would like to award them to erwangoya.
0
 

Author Closing Comment

by:rhservan
ID: 35189306
Excellent solution to my problem.  I just had to work with it more to get to resolution.  Would have appreciated some additional follow up though.  I have attached my final query which works with most of the elements you provided.  Look near the 2nd to last comment
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question