• Status: Solved
• Priority: Medium
• Security: Public
• Views: 339

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

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
rhservan
1 Solution

Commented:

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

select 'Filter1', 'Filter2', 'Filter3'
union all
select cast(@count1 as varchar), cast(@count2 as varchar), cast(@count3 as varchar)
0

Commented:
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

Commented:
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

Commented:
#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 Commented:
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 Commented:
@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)

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

Commented:
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 Commented:
@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 Commented:
Also,  there are no nulls.
0

Author Commented:
0

Author Commented:
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 Commented:
There has been no solution to this prolem in responses above.
Please disregard this question and respond to new submitted question  regarding same problem
0

Author Commented:
This solution was derived based on erwangoya:
Thanks E:

Countwith6col-1row.txt
0

Author Commented:
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 Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.