Reputation: 835
Have below Column in table product :
a | b | cat
--------------
2 | 4 | 1
3 | 1 | 1
4 | 4 | 1
2 | 1 | 1
3 | 2 | 1
5 | 3 | 1
8 | 7 | 2
5 | 3 | 2
$sql = "SELECT
SUM(CASE
WHEN
cat = 1
AND
a > b
THEN 1 else 0 end) as prdplus
FROM product
";
in above query i am getting value for sum or count of a greater then b
which is 3>1 , 2>1 , 3>2 , 5>3
hence output of above query will be : 4
prdplus
-------
4
what i need is count value have only +1 in above count of 4
thats with above query now i need +1 value count
3-1 = 2
2-1 = 1 // just explanation purpose , output i need in -> plus1
3-2 = 1 // just explanation purpose , output i need in -> plus1
5-3 = 2
hence with above query i need only +1 count which is 2
i am not getting how to write case within case including above query as i need both count to show in my table as below
Output Final :
prdplus | plus1
----------------
4 | 2
Upvotes: 1
Views: 540
Reputation: 3096
Check This.
SELECT
SUM(CASE
WHEN
a > b
THEN 1 else 0 end) as prdplus
,
SUM(CASE WHEN a - b = 1
THEN 1
else 0
end) as plus1
FROM
(
select 2 as a , 4 as b union
select 3 , 1 union
select 4 , 4 union
select 2 , 1 union
select 3 , 2 union
select 5 , 3
)
a
Upvotes: 1
Reputation: 204756
SELECT SUM(CASE WHEN a > b
THEN 1
else 0
end) as prdplus,
SUM(CASE WHEN a - b = 1
THEN 1
else 0
end) as prdplus1
FROM product
Upvotes: 1