SQL select count with multiple cases

Question

I want a column where I count transactions where itemtype contains either one of two specified values or location contains one of two specified values. The first two columns (itemtype and location) gives correct figures, but the third seems to count all transactions, irrespective of itemtype or location. What am I doing wrong?

SELECT 
  COUNT(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') THEN 1 END) 'itemtype',
  COUNT(CASE WHEN location IN ('Lattlast','Appelhyllan') THEN 1 END) 'location',
  COUNT(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') THEN 1 
             WHEN location IN ('Lattlast','Appelhyllan') THEN 1  
             ELSE 0
        END) 'total'  
FROM statistics

Hi there, which DBMS are you using (e.g. SQL Server, MySQL etc.) — DKyleo
– DKyleo, Commented Jan 14, 2019 at 9:51
@Klas Just change the ELSE 0 to ELSE NULL or remove it as ELSE NULL is already implied. — arahman
– arahman, Commented Jan 14, 2019 at 9:54

Salman Arshad · Accepted Answer · 2019-01-14 10:01:48Z

1

COUNT counts not null values which means it also "counts" the 0 from the ELSE part. Remove the ELSE part altogether:

SELECT 
  COUNT(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') THEN 1 END) 'itemtype',
  COUNT(CASE WHEN location IN ('Lattlast','Appelhyllan') THEN 1 END) 'location',
  COUNT(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') THEN 1 
             WHEN location IN ('Lattlast','Appelhyllan') THEN 1
        END) 'total'  
FROM statistics

answered Jan 14, 2019 at 10:01

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2019-01-14 09:55:26Z

1

You don't need to add ELSE part :

SELECT COUNT(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') THEN 1 END) AS itemtype,
       COUNT(CASE WHEN location IN ('Lattlast','Appelhyllan') THEN 1 END) AS location,
       COUNT(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') THEN 1 
                  WHEN location IN ('Lattlast','Appelhyllan') THEN 1  
             END) AS total
FROM statistics;

answered Jan 14, 2019 at 9:55

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

Fahmi · Accepted Answer · 2019-01-14 09:55:59Z

1

For last one you can use SUM instead of COUNT like below -

SELECT 
  COUNT(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') THEN 1 END) 'itemtype',
  COUNT(CASE WHEN location IN ('Lattlast','Appelhyllan') THEN 1 END) 'location',
  sum(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') THEN 1 
  WHEN location IN ('Lattlast','Appelhyllan') THEN 1  
 END) 'total'  

FROM statistics

edited Jan 14, 2019 at 9:55

answered Jan 14, 2019 at 9:52

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

1 Comment

arahman Over a year ago

An alternative would have been to change the ELSE 0 to ELSE NULL in the original COUNT version. Or remove it as ELSE NULL is already implied. :)

Shushil Bohara · Accepted Answer · 2019-01-14 09:59:39Z

1

I think, the third condition would be as below

SELECT
COUNT(CASE WHEN itemtype IN ('BARNTAL','STORSTIL') OR location IN ('Lattlast','Appelhyllan') THEN 1 END) AS total
FROM statistics;

answered Jan 14, 2019 at 9:59

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

Collectives™ on Stack Overflow

SQL select count with multiple cases

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related