1

Consider I have following table products

group   ads status
X       ad1 ACTIVE
X       ad2 NOT_BUYABLE
X       ad3 PAUSED
Y       ad4 ACTIVE
Y       ad5 SUSPENDED
Y       ad6 PAUSED
Z       ad7 ACTIVE
Z       ad8 OUT_OF_BUDGET
Z       ad9 ARCHIVED

All I want is to filter distinct group

For example, I want to filter the group where

  1. the product status != NOT_BUYABLE
  2. AND the product status = ACTIVE

consider Group X:

  • ad1 = ACTIVE
  • ad2 = NOT_BUYABLE
  • ad3 = PAUSED

This filter would ignore Group X

consider Group Y:

  • ad4 = ACTIVE
  • ad5 = SUSPENDED
  • ad6 = PAUSED

This filter would return Group Y

consider Group Z:

  • ad7 = ACTIVE
  • ad8 = OUT_OF_BUDGET
  • ad9 = ARCHIVED

This filter would return Group Z

so final output should be

group
Y
Z

This means the user only wants to filter product that doesn't have any NOT_BUYABLE status values but has at least 1 ACTIVE status.

when I apply != operator I have to make sure none of the product under that group has those staus

This SQL query is wrong

Select  distinct `group`   from products
where status != 'NOT_BUYABLE' and status = 'ACTIVE';

Let me know what will be the SQL query

4 Answers 4

3

Plan A:

SELECT a.group
    FROM ( SELECT DISTINCT `group` FROM tbl ) AS a
    WHERE
         EXISTS ( SELECT * FROM tbl WHERE group = a.group
                                      AND status = 'ACTIVE' )
     AND
     NOT EXISTS ( SELECT * FROM tbl WHERE group = a.group
                                      AND status = 'NOT_BUYABLE' )

Plan B:

SELECT a.group,
       GROUP_CONCAT(status) AS stati
    FROM tbl AS a
    GROUP BY `group`
    HAVING      FIND_IN_SET('ACTIVE',      stati)
        AND NOT FIND_IN_SET('NOT_BUYABLE', stati)

(To get rid of the second column, you need to wrap that in another SELECT.)

(Suggest changing your column name away from the keyword "group".)

Recommend this for both queries:

 INDEX(`group`, `status`)

(I don't know which Plan will be faster.)

1
  • I have modified it like this Select group ,count(*)- SUM(CASE WHEN status != 'NOT_BUYABLE' THEN 1 ELSE 0 END) NOT_BUYABLE ,count(*)- SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) ACTIVE from testproductads group by group having NOT_BUYABLE = 0 and ACTIVE > 0;
    – Mubbashar
    Jun 22, 2018 at 7:36
2

Similar to Rick James' plan B but using MIN(<CASE expression>) as aggregate instead of - the possibly more expensive - GROUP_CONCAT() and FIND_IN_SET():

Plan C (C1/C2: with and without the WHERE)

SELECT a.group
    FROM tbl AS a
 -- WHERE a.status IN ('ACTIVE', 'NOT_BUYABLE')
    GROUP BY a.group
    HAVING  MIN(CASE WHEN a.status = 'ACTIVE' THEN 1 END) = 'ACTIVE'
        AND MIN(CASE WHEN a.status = 'NOT_BUYABLE' THEN 1 END) IS NULL ;
3
  • Or maybe something using ANY()?
    – Rick James
    Jun 22, 2018 at 15:18
  • @RickJames yeah, I suppose it could be wrten with ANY as well. To be honest, I'd use the EXISTS approach in your answer or similar. I've seen various cases of this type of queries and - in many real life distributions of data - the EXISTS method beats the GROUP BY by far... Jun 22, 2018 at 15:25
  • Of course, when you data is a few thousands or million rows, the various methods make very small difference. Jun 22, 2018 at 15:25
1

Variation from other answers in thread, plan E seems to be the case:

SELECT grp
FROM testproductads
GROUP BY grp
HAVING COUNT(CASE WHEN STATUS <> 'NOT_BUYABLE' THEN 1 END) = COUNT(1)     
   AND COUNT(CASE WHEN STATUS = 'ACTIVE' THEN 1 END) >= 1

Mubbashar variant for buyable is more efficient since it does not have to COUNT(1)

SELECT grp
FROM testproductads
GROUP BY grp
HAVING COUNT(CASE WHEN STATUS = 'NOT_BUYABLE' THEN 1 END) = 0     
   AND COUNT(CASE WHEN STATUS = 'ACTIVE' THEN 1 END) >= 1
0

Plan D can be

SELECT
`group`
    , SUM(CASE 
            WHEN STATUS = 'NOT_BUYABLE'
                THEN 1
            ELSE 0
            END) NOT_BUYABLE
    , SUM(CASE 
            WHEN STATUS = 'ACTIVE'
                THEN 1
            ELSE 0
            END) ACTIVE
FROM testproductads
GROUP BY
`group`
HAVING NOT_BUYABLE = 0
    AND ACTIVE > 0;
1
  • 1
    A shorter way to SUM: SUM(status = 'ACTIVE') . This works because a boolean expression is treated as 0 for false and 1 for true.
    – Rick James
    Jun 22, 2018 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.