CODEWITHSUNDEEP
sqlselectwhere-clause
steventnorris
steventnorris

Reputation: 5876

SELECT with Different WHERE Conditions

I would like to run a 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 a way to do this?

Upvotes: 4

Views: 1506

Answers (5)

hmmftg
hmmftg

Reputation: 1681

Why Don't you use this?

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

Upvotes: 0

bpgergo
bpgergo

Reputation: 16037

It is not clear what do you want.

I suppose you want to a select, that returns one 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

Upvotes: 0

Taryn
Taryn

Reputation: 247620

You can use a 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

Upvotes: 4

juergen d
juergen d

Reputation: 204746

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

Upvotes: 4

Rob Paller
Rob Paller

Reputation: 7786

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

Upvotes: 1

Related Questions