Using SQL Count in a Case Statement

using sql count in a case statement

SELECT 
    COUNT(CASE WHEN rsp_ind = 0 then 1 ELSE NULL END) as "New",
    COUNT(CASE WHEN rsp_ind = 1 then 1 ELSE NULL END) as "Accepted"
from tb_a

You can see the output for this request HERE

Conditional COUNT within CASE statement

You were nearly there! I've made a few changes:

SELECT 
  DATEFROMPARTS(YEAR(t1.Date), MONTH(t1.Date),1) AS Draft_Date, 
  Membership,
  COUNT(CASE WHEN t1.Membership = 5 AND t1.Service_Type = 'A' THEN 1 END) as m5stA,
  COUNT(CASE WHEN t1.Membership = 2 AND t1.Service_Type IN ('J','C') THEN 1 END) as m2stJC
FROM Table1 t1 
GROUP BY YEAR(t1.Date), MONTH(t1.Date), Membership

Changes:

• Avoid using apostrophes to alias column names, use ascii standard " double quotes if you must
• When doing a conditional count, put the count outside the CASE WHEN, and have the case when return something (any non null thing will be fine - i used 1, but it could also have been 'x' etc) when the condition is met. Don't put an ELSE - CASE WHEN will return null if there is no ELSE and the condition is not met, and nulls don't COUNT (you could also write ELSE NULL, though it's redundant)
• Qualify all your column names, always - this helps keep the query working when more tables are added in future, or even if new columns with the same names are added to existing tables
• You forgot a THEN in the second WHEN
• You don't necessarily need to GROUP BY the output of DATEFROMPARTS. When a deterministic function is used (always produces the same output from the same inputs) the db is smart enough to know that grouping on the inputs is also fine
• Your example data didn't contain any data that would make the COUNT count 1+ by the way, but I'm sure you will have other conditional counts that work out (it just made it harder to test)

How to use count for a case statement

I feel that you should be doing the outer count rollups over a subquery which aggregates by order:

SELECT
    COUNT(CASE WHEN sales < 5000  THEN 1 END) AS Low,
    COUNT(CASE WHEN sales < 9999  THEN 1 END) AS Medium,
    COUNT(CASE WHEN sales >= 9999 THEN 1 END) AS High
FROM
(
    SELECT o.[Order ID], SUM(o.Sales) AS sales
    FROM Orders o
    INNER JOIN Returns r ON o.[Order ID] = r.[Order ID]
    GROUP BY o.[Order ID]
) t;

That being said, I don't actually know what the purpose of joining Orders to the Returns table is. If you intend to only find sales amounts from orders which have been returned, then maybe this makes sense. Otherwise, maybe it doesn't make sense.

How best to Count(*) with a CASE STATEMENT?

I tend to like sum()

SELECT
SUM(CASE WHEN <conditions> THEN 1 ELSE 0 END) as conditionalcountall
FROM TABLE

SQL Case statement with Count?

Use aggregation:

select p.person_nbr,
       (case when min(ml1.mstr_list_item_desc) = max(ml1.mstr_list_item_desc)
             then min(ml1.mstr_list_item_desc)
             else 'Unknown'
        end) as final_ethnicity
from person_table p left join
     person_ethnicity_xref eth1
     on p.person_id = eth1.person_id left join
     mstr_lists ml1
     on eth1.ethnicity_item_id = ml1.mstr_list_item_id
group by p.person_nbr;

Note: This slightly tweaks your logic. If there a multiple ethnicities and they are all the same, then that value is used.

MySQl using COUNT with CASE statement

You should count 1 for a match, and count NULL when there is no match:

SELECT
    account_id,
    COUNT(CASE WHEN action_type = 1 AND action_name = 'like' THEN 1 END) AS `like`,
    COUNT(CASE WHEN action_type = 1 AND action_name = 'superLike' THEN 1 END) AS superLike,
    COUNT(CASE WHEN action_type = 2 THEN 1 END) AS follow
FROM instagram_actions_histories
WHERE account_id IN (1)
GROUP BY account_id;

The problem with the current logic of your CASE expressions is that COUNT will count any non null value as one count. So zero also would be counted.

Note that your current logic would have worked using SUM to take the conditional aggregations, e.g.

SUM(CASE WHEN action_type = 1 AND action_name = 'like'
         THEN 1 ELSE 0 END) AS `like`

In this case, to turn off the aggregation for non matching records, we in fact can use zero, because summing zero does not affect the sum.

count and sum in case statement

The three are equivalent. All of them count the number of rows that meet the particular condition (salary > 100000). All return 0/1 and would not return NULL values for the column.

From a performance perspective, all should be equivalent as well. I have a personal preference for the first version. I consider the third to be unnecessarily verbose because else NULL is the default for a case expression.

Count Nested Case in SQL

COUNT( CASE trans.id_user  WHEN 1 THEN 1  
       CASE trans.id_train WHEN 1 THEN 1
                           ELSE NULL END ) AS total_ticket

Unclear construction. Looks like you need one of below variants.

COUNT( CASE WHEN trans.id_user  = 1 THEN 1  
            WHEN trans.id_train = 1 THEN 1
                                    ELSE NULL END ) AS total_ticket
-- which may be simplified to 
SUM(1 IN (trans.id_user, trans.id_train)) AS total_ticket

COUNT( CASE WHEN trans.id_user  = 1 
             AND trans.id_train = 1 THEN 1
                                    ELSE NULL END ) AS total_ticket
-- which may be simplified to 
SUM(trans.id_user  = 1 AND trans.id_train = 1) AS total_ticket

---

Related Topics