Solved

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

Posted on 2011-03-16
15
325 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

739 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