CODEWITHSUNDEEP
sqlhive
vkaul11
vkaul11

Reputation: 4214

how to get column that satisfies a condition across several rows

IF we have data like that I got after grouping all transaction data into month and item_count per month :

cust_id    item_month item_count
    64        1          1
    64        2          1
    64        3          1
    65        1          1
    66        2          1
    66        3          2

Original table is

cust_id  item_name transaction_date
  64       bag      2017-01-01
  64       bag      2017-02-05
  64       bag      2017-03-07
  65       bottle   2017-01-10
  66       gloves   2017-02-11
  66       bag      2017-03-05
  66       kite     2017-03-02

and I want to only get cust_ids for which in each of the 3 months (1,2 and 3) we have item_count>=1. How do we get that? Basically in the above case the answer would be 64.

SELECT 
  t1.cust_id
(SELECT 
   cust_id
FROM 
   table
WHERE item_count>=1 AND item_month=1) t1
JOIN 
(SELECT
   cust_id
FROM 
   table
WHERE item_count>=1 AND item_month=2) t2
ON t1.cust_id=t2_cust_id
JOIN
(SELECT
    cust_id
 FROM 
   table
 WHERE item_count>=1 AND item_month=3) t3
ON t1.cust_id=t3.cust_id

Is there a more efficient way to do this ? Perhaps directly from transaction_data?

Upvotes: 1

Views: 34

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Try this query:

SELECT cust_id
FROM table
WHERE item_count >= 1
GROUP BY cust_id
HAVING COUNT(DISTINCT item_month) = 3

Upvotes: 1

Randy
Randy

Reputation: 16677

I do not understand where you get 64.. but here is how you would find customers with a value in each of the 3 months.

SELECT cust_id
FROM table
where item_month in (1,2,3)
GROUP BY cust_id
HAVING count( distinct item_month ) = 3

Upvotes: 0

Related Questions