Count Nested Case in SQL

Question

I want to display a rows train with id_user and id_train Can I nested a case statement in count function sql?

My SQL is here but it errors

SELECT ticket.*
       , COUNT( CASE trans.id_user WHEN 1 THEN 1  
CASE trans.id_train WHEN 1 THEN 1
ELSE NULL END) AS total_ticket
       , SUM(train.price) AS total_price
       , user.* 
FROM train JOIN trans ON trans.id_train = kereta.id_train 
JOIN user ON trans.id_user = user.id_user 
JOIN ticket ON ticket.train = train.id_train;

score 1 · Accepted Answer · answered Feb 27 '20 at 07:01

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

score 0 · Answer 2 · answered Feb 27 '20 at 06:41

You can do it like this:

select COUNT( CASE trans.id_user WHEN 1 
                                 THEN 1  
                                 ELSE NULL 
              END) first_cnt
       , COUNT( CASE trans.id_train WHEN 1 
                                    THEN 1  
                                    ELSE NULL 
                END) second_cnt
from trans;

But if you want to add any other column to the select you need to group by that column:

select trans.some_column
       , COUNT( CASE trans.id_user WHEN 1 
                                 THEN 1  
                                 ELSE NULL 
              END) first_cnt
       , COUNT( CASE trans.id_train WHEN 1 
                                    THEN 1  
                                    ELSE NULL 
                END) second_cnt
from trans
group by trans.some_column;

In your case that means that you would need to group by all the columns from tickets because you use this: ticket.* , all the columns from users because you use user.*

Also please be aware that all of the above can be applyed to the SUM from your select.

Count Nested Case in SQL

2 Answers2