Solved

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

Posted on 2011-03-16
15
327 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 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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