asked
count but don't show zeros
This is a follow up to question "Sum per another column" Now I have a count based on another column. I have many count functions like this one below. The problem is I don't want to see any that have zero. Is there a way to show only values >1 without adding a bunch of Where statements afte the from? Can it be added to the count statement?? ,COUNT(case when Issue_Status = 'Open' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End) as Sev1Red
Unfortunately, there is no cheap way to do it. Here is a couple of options you can choose: add the following line to your script directly after the group by line: having COUNT(case when Issue_Status = 'Open' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End) > 0 Another option is to use your original select as sub-select or CTE and then add and Sev1Red > 0 to your where clause. <\!-- **Begin Edit** Here are 2 versions of the query matching the options in my answer. Both will work. Option 1 (with having clause): Select A.PrimaryBusinessOwner, CASE WHEN B.MILESTONESTATUS < 49 THEN 'Red' WHEN B.MILESTONESTATUS BETWEEN 50 AND 74 THEN 'Yellow' WHEN B.MILESTONESTATUS > 75 THEN 'Green' ELSE 'Unknown' END [Overall Milestone Status], COUNT ( case when Issue_Status = 'Open' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End ) as Sev1Red FROM [metview_owner].[tblAudit] A (nolock) LEFT JOIN ( SELECT ISSUE_NUMBER, SUM(WEIGHT) AS MilestoneStatus FROM METVIEW_OWNER.TBLAUDIT_MITIGATION WHERE ENDDATE > GETDATE() OR Completepercent = '100' GROUP BY ISSUE_NUMBER ) B ON A.ISSUE_NUMBER = B.ISSUE_NUMBER GROUP BY A.PrimaryBusinessOwner, B.MILESTONESTATUS HAVING COUNT ( case when Issue_Status = 'Open' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End ) > 0; Option 2 (using sub-select to avoid having clause): select sub.PrimaryBusinessOwner, sub.[Overall Milestone Status], sub.Sev1Red from ( Select A.PrimaryBusinessOwner, CASE WHEN B.MILESTONESTATUS < 49 THEN 'Red' WHEN B.MILESTONESTATUS BETWEEN 50 AND 74 THEN 'Yellow' WHEN B.MILESTONESTATUS > 75 THEN 'Green' ELSE 'Unknown' END [Overall Milestone Status], COUNT ( case when Issue_Status = 'Open' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End ) as Sev1Red FROM [metview_owner].[tblAudit] A (nolock) LEFT JOIN ( SELECT ISSUE_NUMBER, SUM(WEIGHT) AS MilestoneStatus FROM METVIEW_OWNER.TBLAUDIT_MITIGATION WHERE ENDDATE > GETDATE() OR Completepercent = '100' GROUP BY ISSUE_NUMBER ) B ON A.ISSUE_NUMBER = B.ISSUE_NUMBER GROUP BY A.PrimaryBusinessOwner, B.MILESTONESTATUS ) sub where sub.Sev1Red > 0; **End Edit** --> Oleg
I really need to eliminate the zeros. Your suggestion of adding "having" and ">0" did not work. Using a CTE was something I tired before my post but had difficulty because of the Left Join I need to use. Could you please help me put this in a CTE and eliminate the zero results. I removed all the other counts except one. Select A.PrimaryBusinessOwner ,CASE WHEN B.MILESTONESTATUS < 49 THEN 'Red' WHEN B.MILESTONESTATUS BETWEEN 50 AND 74 THEN 'Yellow' WHEN B.MILESTONESTATUS > 75 THEN 'Green' ELSE 'Unknown' END 'Overall Milestone Status' ,COUNT(case when Issue_Status = 'Open' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End) as Sev1Red FROM [metview_owner].[tblAudit] A (nolock) LEFT JOIN (SELECT ISSUE_NUMBER, SUM(WEIGHT) AS MilestoneStatus FROM METVIEW_OWNER.TBLAUDIT_MITIGATION WHERE ENDDATE > GETDATE() OR Completepercent = '100' GROUP BY ISSUE_NUMBER) B ON A.ISSUE_NUMBER = B.ISSUE_NUMBER GROUP BY A.PrimaryBusinessOwner, B.MILESTONESTATUS
Yes, it should work. In your specific query if you add
having COUNT(case when Issue_Status = 'Open'
and B.MILESTONESTATUS < 49
and currentSeverity = '1'THEN 1 else NULL End) > 0 directly under your query, i.e. after the line reading
GROUP BY A.PrimaryBusinessOwner, B.MILESTONESTATUSthen it will eliminate all the rows which have a value of zero in the Sev1Red column of your final result.
Counting only values greater than 1? Not directly (at least, not in the context of this question), but you can do this which is equivalent: , SUM (case when Issue_Status = 'Open' AND B.MileStoneStatus < 49 AND currentSeverity = '1' THEN 1 ELSE 0 END) AS Sev1Red EDIT - And, if you don't want to see the rows where the above sum works out to zero (which seems to be the case), then you would add the following to your GROUP BY clause GROUP BY ... HAVING (CASE WHEN Issue_Status = 'Open' AND B.MileStoneStatus < 49 AND CurrentSeverity = '1' THEN 1 ELSE 0 END) > 0
How is it different from the original query in question? Summing zeroes and ones is the same as counting ones and nulls, same result. Also, the question is asking about not including the rows which result in Sev1Red = 0, so the need to add HAVING is still there. That is:
having SUM (case when Issue_Status = 'Open'
AND B.MileStoneStatus < 49
AND currentSeverity = '1' THEN 1 ELSE 0 END) > 0 directly under the group by statement
@LtDan given the doubts cast by @Oleg and yourself, it's possible I have misunderstood your original requirement. If it's that you're not wanting to see rows where the sum is zero, then put a HAVING subclause in the GROUP BY, as @Oleg says.
Ok, in your 1st option, I see what your saying, but then I add the second Having Count it bombs. how do I add a 2dn 3rd etc count to this option 1. Here is one of the ways I tried to add a second count. Select A.PrimaryBusinessOwner, CASE WHEN B.MILESTONESTATUS < 49 THEN 'Red' WHEN B.MILESTONESTATUS BETWEEN 50 AND 74 THEN 'Yellow' WHEN B.MILESTONESTATUS > 75 THEN 'Green' ELSE 'Unknown' END [Overall Milestone Status] ,COUNT(case when Issue_Status = 'Open' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End) as Sev1Red ,COUNT(case when Issue_Status = 'Closed' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End) as Sev1Red FROM [metview_owner].[tblAudit] A (nolock) LEFT JOIN (SELECT ISSUE_NUMBER, SUM(WEIGHT) AS MilestoneStatus FROM METVIEW_OWNER.TBLAUDIT_MITIGATION WHERE ENDDATE > GETDATE() OR Completepercent = '100' GROUP BY ISSUE_NUMBER) B ON A.ISSUE_NUMBER = B.ISSUE_NUMBER GROUP BY A.PrimaryBusinessOwner, B.MILESTONESTATUS HAVING COUNT (case when Issue_Status = 'Open' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End) > 0 Having COUNT(case when Issue_Status = 'Closed' and B.MILESTONESTATUS < 49 and currentSeverity = '1'THEN 1 else NULL End) > 0
@LtDan The real question is then what do you want to do? For example, if your first count is zero, but the second is not for the same record? I suppose that you still want to display such record, so only those records which have zeroes for both should be eliminated. In this case, please replace every occurence of the word HAVING (starting from the second one) with the word OR. For example,
HAVING
COUNT (case when Issue_Status = 'Open'
and B.MILESTONESTATUS < 49
and currentSeverity = '1'THEN 1 else NULL End) > 0
OR
COUNT(case when Issue_Status = 'Closed'
and B.MILESTONESTATUS < 49
and currentSeverity = '1'THEN 1 else NULL End) > 0 You can optionally choose to use having count(first case clause) + count(second case clause) + etc > 0. For example,
HAVING
COUNT (case when Issue_Status = 'Open'
and B.MILESTONESTATUS < 49
and currentSeverity = '1'THEN 1 else NULL End)
+
COUNT(case when Issue_Status = 'Closed'
and B.MILESTONESTATUS < 49
and currentSeverity = '1'THEN 1 else NULL End) > 0
@LtDan Of course they are. Suppose you have the first count returning **1** or more but the second count returning **0** for the same record. This should mean that the record must be shown. If you prefer the record to be not shown though the first count is greater than zero then you can use the first version (using **OR**) and replace **OR** with **AND**. Please do it only if this is actually what you want, but beware that this does not appear to be a sound logic.
