Solved

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

Posted on 2011-03-16
15
320 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
15 Comments
 
LVL 32

Accepted Solution

by:
ewangoya 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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…
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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