0

I have a table where i have two columns in the below fashion

sale_id status
268 5
268 1
268 1
268 1
283 5
283 5
283 5
284 5
284 1
284 1
284 1
284 1
284 1
284 1

As you can see, we have sale_id and status field. I need to write an SQL query where I find all sales_id where all rows have become 5.

So I would want to create a third column which mention in front of the sales_id that this sales_id is expired (if all rows became 5). For example, look at the below table. From the above table, I would want to create the below table

sale_id activity
268 active
283 expired
284 active

because sales_id 268 and 284 have some rows 5 but also some rows 1, therefore they are active. sales_id 283 have all rows 5, therefore, it should show expired in the activity.

Can someone please help how to comeup with this query

Arif
  • 377
  • 2
  • 5
  • 21

1 Answers1

1

You may use conditional aggregation to find the count of ids where status = 5 and compare it with the total count of id as the following:

SELECT sale_id,
   CASE WHEN COUNT(*) = COUNT(CASE status WHEN 5 THEN 1 END)
        THEN 'expired'
        ELSE 'active'
   END AS activity
FROM table_name
GROUP BY sale_id
ORDER BY sale_id;

Or you may use EXISTS operator as the following:

SELECT T.sale_id,
       CASE WHEN EXISTS(SELECT 1 FROM table_name D WHERE D.sale_id = T.sale_id AND D.status<>5)
            THEN 'active'
            ELSE 'expired'
       END AS activity
FROM table_name T
GROUP BY T.sale_id
ORDER BY sale_id;

See a demo.

ahmed
  • 9,071
  • 3
  • 9
  • 22