Using case with an aggregate function-CodePudding

I'm trying to count the number of customers that bought more than 10 items from a store a specific month that bought less than 10 items from the previous month (only has 2 months, 01 and 02)

For example,

id        items_bought        month
0001        11                  02
0001         9                  01
0003         8                  02
0003         8                  01
0005         13                 02
0006         16                 02
0006         17                 01

this would return a count of 1.

My idea was to do a case statement like

   case 
     when (month = '02' and items_bought > 10) and (month = '01' and items_bought < 10) then 1
   else 0
end as c

and then kind of do a select c, count(c) where c = 1 but then I realized this wouldn't help since I would need to group by ids' and I'm kind of stuck where to continue.

CodePudding user response：

You use Sum over Case like this:

SELECT id
FROM tbl
GROUP BY id
HAVING  SUM(   CASE
                  WHEN (
                           month = '02'
                           AND items_bought > 10
                       )
                       AND
                       (
                           month = '01'
                           AND items_bought < 10
                       ) THEN
                      1
                  ELSE
                      0
              END
          )=1

CodePudding user response：

For your specific example you could use count with a correlated exists

select Count(Id)
from t 
where t.items_bought > 10 and t.month = 2
and exists (
  select * from t t2 
  where t2.id = t.id and t2.items_bought < 10 and t2.month = t.2month - 1
);