SELECT with various WHERE clauses

Question

I would like to run select something like this.

SELECT COUNT(*) WHERE switch=0 AND detail=1 AS zeroone AND COUNT(*) WHERE switch=0 AND detail=2 AS zerotwo AND COUNT(*) WHERE switch=1 AND detail=1 AS oneone AND COUNT(*) WHERE swithc=1 AND detail=2 AS onetwo FROM tablename 

Is there any way to do this?

Answer 1

You can use the CASE statement in your SELECT to get the results:

 SELECT SUM(case when switch=0 AND detail=1 then 1 else 0 end) as zeroone , SUM(case when switch=0 AND detail=2 then 1 else 0 end) as zerotwo , SUM(case when switch=1 AND detail=1 then 1 else 0 end) as oneone , SUM(case when switch=1 AND detail=2 then 1 else 0 end) as onetw FROM tablename 

Answer 2

 SELECT sum(switch=0 AND detail=1) AS zeroone, sum(switch=0 AND detail=2) AS zerotwo, sum(switch=1 AND detail=1) AS oneone, sum(switch=1 AND detail=2) AS onetwo FROM tablename 

Answer 3

 SELECT SUM(CASE WHEN switch = 0 and detail = 1 THEN 1 ELSE NULL END) AS zeroone , ... FROM TableName 

Answer 4

It is unclear what you want.

I assume that you want to select which returns a single row having 4 columns:

 SELECT COUNT(*) WHERE switch=0 AND detail=1 AS zeroone, COUNT(*) WHERE switch=0 AND detail=2 AS zerotwo, COUNT(*) WHERE switch=1 AND detail=1 AS oneone, COUNT(*) WHERE swithc=1 AND detail=2 AS onetwo FROM tablename 

Answer 5

Why aren't you using this?

 SELECT COUNT(*) FROM tablename GROUP BY switch, detail