Count Case and When Statement in MySQL

COUNT CASE and WHEN statement in MySQL

Use:

SELECT SUM(CASE 
             WHEN t.your_column IS NULL THEN 1
             ELSE 0
           END) AS numNull,
       SUM(CASE 
             WHEN t.your_column IS NOT NULL THEN 1
             ELSE 0
           END) AS numNotNull
  FROM YOUR_TABLE t

That will sum up the column NULL & not NULL for the entire table. It's likely you need a GROUP BY clause, depending on needs.

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

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 with case statement not working properly

Replace

COUNT(CASE
      WHEN c.card_id = 1 THEN 1
      WHEN c.card_id = 2 THEN 2
      WHEN c.card_id = 3 THEN 3
      END) AS points,

with

SUM(CASE
    WHEN c.card_id = 1 THEN 1
    WHEN c.card_id = 2 THEN 2
    WHEN c.card_id = 3 THEN 3
    END) AS points,

because with count you just count while you want the sum really. If you count the numbers 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 3 you get fifteen, if you add them up you get seventeen.

However, there is obviously a card table the card_id refers to. This card table should naturally contain the value of the card. So join with the cards table and use

SUM(card.value)

instead of the above.

Count Case Statement - When One Field Greater Than Another

For the count value You could use sum instead of count adding 1 when the condition is true and 0 when false

In sql for aggregated select the select for columns not in aggregated function and not mentioned in group by is deprecated, in the most recent version of mmysql is not allowed and for the older version the result for these values in unpredicatble so you should in group by then column that you have not in aggregation function in select eg:

    select b.brandName
        , b.BrandCode
        , p.ProductVendorStockNumber
        ,sum(Case When p.IMAP > p.MSRP THEN 1 ELSE 0  END) as my_count
    from products p
    join brands b on p.brandID = b.brandID 
    where b.assignedTo = 'Steve' and p.IMAP > p.MSRP and status = 1
    GROUP BY b.BrandName, b.BrandCode,  p.ProductVendorStockNumber

or filter the result using the rows without aggregation and a join on the right aggregated rows

Using CASE on COUNT in MySQL

SELECT 
    *,
    COUNT(Names) AS COUNT_OF_N,
    CASE WHEN COUNT(Names) <= 3
             THEN COUNT(Names) * 2
         WHEN COUNT(Names) <= 6
             THEN COUNT(Names) * 3 
    END AS Multiplied
FROM
    mytable
GROUP BY
    Names

This should do what you need. You'll just have to make sure you cover every case.

COUNT MAX CASE and GROUP BY for recurring type id

You can use SUM()

Query

SELECT `project_id`,
SUM(CASE WHEN `document_type_id` = 1 THEN 1 ELSE 0 END) as "review",
SUM(CASE WHEN `document_type_id` = 2 THEN 1 ELSE 0 END) as "interview",
SUM(CASE WHEN `document_type_id` = 3 THEN 1 ELSE 0 END) as "romance"
FROM projectDocumentList
GROUP BY project_id

Results

| project_id | review | interview | romance |
| ---------- | ------ | --------- | ------- |
| 10         | 3      | 2         | 1       |
| 11         | 0      | 4         | 2       |

demo

Or COUNT(..) can also be used but you need to use NULL instead of 0.

This is because COUNT(..) handles 0, 1 and NULL values different then SUM(..) does.

Query

SELECT `project_id`,
COUNT(CASE WHEN `document_type_id` = 1 THEN 1 ELSE NULL END) as "review",
COUNT(CASE WHEN `document_type_id` = 2 THEN 1 ELSE NULL END) as "interview",
COUNT(CASE WHEN `document_type_id` = 3 THEN 1 ELSE NULL END) as "romance"
FROM projectDocumentList
GROUP BY project_id

Results

| project_id | review | interview | romance |
| ---------- | ------ | --------- | ------- |
| 10         | 3      | 2         | 1       |
| 11         | 0      | 4         | 2       |

demo

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

---

Related Topics