CODEWITHSUNDEEP
pythonsqlsqlitecase
codeKiller
codeKiller

Reputation: 5739

SQL query with case and different conditions

I have a SQL database with a table looking more or less like this one:

channel  type  I1  I2 
    1     A    34   10
    2     A    27   9
    3     B    9    21
    1     A    29   11
    2     B    8    19
    3     A    27   9
    1     B    11   20
    2     B    9    22
    3     A    31   8

I would like to query the table using different criteria in case of type=A or type=B, however I would like to group the results by channel, showing the total hits.

My last approach to the problem is this: (not working)

SELECT  channel, count(channel)
case 
    when type="A" then
        WHERE I1>30 and I2<10 
    when type="B" then 
        WHERE I1>10 and I2<20 
end
from channelResults
group by channel

The expected output would be so simple as:

channel   count
   1         9
   2         20
   3         3

So as an example, the output would show that in channel 1 there are 9 rows (type A or type B it does not matter) that satisfy each condition, that means, for example we have 4 typeA that satisfy condition for typeA + 5 typeB that satisfy condition for typeB, so in total 9.

Upvotes: 0

Views: 176

Answers (5)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can try this:-

SELECT  channel, count(case when type="A" and I1>30 and I2<10 then channel end as count_channel
                            when type="B" and I1>10 and I2<20 then channel end as count_channel)
from channelResults
group by channel

Upvotes: 0

citywall
citywall

Reputation: 235

Here is a fiddle to play around with ... I was testing my SQL while all the other answered ;)

http://sqlfiddle.com/#!9/fd13e/3

Upvotes: 1

W.Prins
W.Prins

Reputation: 1316

There's multiple ways to write this. One way would be to split the query into 2 parts as that's really what your problem states -- one set of criteria for A, another for B. So following that line of thinking:

select channel, count(channel) from (
  --countables type A
  select * from channelResults where type='A' and I1>30 and I2<10 
  union all
  --countables type B
  select * from channelResults where type='B' and I1>10 and I2<20 
) data
GROUP BY data.channel

Another way would be to output the count value (0 or 1) using a case, a bit like what you were doing, and then count (or sum) that colum:

SELECT  
  channel,
  count( --or sum()
    CASE 
      WHEN [type]='A' and I1>30 and I2<10 then 1 
      WHEN [type]='B' and I1>10 and I2<20 then 1 
      ELSE NULL 
    END 
  ) CountValue
FROM channelResults
GROUP BY channel

If this is a bit hard to check/decipher, you could also do this in multiple steps:

WITH count_data AS (
    SELECT  
      *, 
      CASE 
        WHEN [type]='A' and I1>30 and I2<10 then 1 
        WHEN [type]='B' and I1>10 and I2<20 then 1 
        ELSE NULL 
      END AS CountValue
    FROM channelResults
) 
SELECT channel, COUNT(CountValue) CountValue 
FROM count_data
GROUP BY channel

You can then initially write the bottom query as:

select * from count_data

Then once you're happy with how the raw data and how case works, you can then group it as I showed above.

Hope that helps.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT  channel, --count(channel)
case when type="A" and I1>30 and I2<10 then count(channel) 
     when type="B" and I1>10 and I2<20 then count(channel)
end as count
from channelResults
group by channel

Upvotes: 1

lunaks
lunaks

Reputation: 136

Try this

    select channel, 
           count(channel)
      from channelResults
     where type = 'A' and I1 > 30 and I2 < 10 or
           type = 'B' and I1 > 10 and I2 < 20
  group by channel

but still i'm not sure if this is what you want as output, if it is not please be more specific.

Upvotes: 2

Related Questions