CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 4728

MySQL query where data shown in rows and not columns

I need to find the customerIDs of all customers who have ordered a certain quantity of particular products. For example, I want to get all customerIDs who have ordered 2 or more cheese or 5 or more bread. This should give me 1,4 and 3

customerID  product     qty
1           cheese      3
1           bread       2
2           cheese      1
2           pizza       4
3           bread       10
3           tomato      4
4           cheese      2

It would be much easier if my table structure was altered to have a column for cheese, bread, pizza, bread and tomato. Unfortunately this isn't possible.

EDIT: The customerID/product combo is unique. I would also like to be able to search for when all conditions are met. So, where 3 or more cheese AND 2 or more bread.

Upvotes: 1

Views: 66

Answers (3)

spencer7593
spencer7593

Reputation: 108370

EDIT My bad... I misread the specification. The examples below returns customers that had 'cheese' qty>=2 AND 'bread' qty >=5. The specification was for OR rather than AND.

Here's an example of one approach (out of several viable approaches) to returning the specified a resultset that wasn't specified... customers that had BOTH cheese qty>=2 AND bread qty>=5:

SELECT a.customer_id
  FROM ( SELECT p.customer_id 
           FROM mytable p
          WHERE p.product         = 'cheese'
          GROUP BY p.customer_id
         HAVING SUM(p.qty)        >= 2
       ) a
  JOIN ( SELECT q.customer_id 
           FROM mytable q
          WHERE q.product         = 'bread'
          GROUP BY q.customer_id
         HAVING SUM(q.qty)        >= 5
       ) b
    ON b.customer_id = a.customer_id

I've assumed you wanted to check the qty for a given product and given customer as the "total" qty, from all of the matching rows, not just checking for an individual row with qty over the specified value. If you only want to check qty on individual rows, then remove the predicate on the aggregate i.e. HAVING SUM(qty) >= n, and add a predicate in the WHERE clause AND qty >= n.

EDIT To get the result of customers that had EITHER, we could use a similar approach, getting the list of customers that had cheese qty>=1, and combining that with the list of customer that had bread qty>=5, and get a distinct list.

  ( SELECT p.customer_id 
      FROM mytable p
     WHERE p.product         = 'cheese'
     GROUP BY p.customer_id
    HAVING SUM(p.qty)        >= 2
  ) 
  UNION 
  ( SELECT q.customer_id 
      FROM mytable q
     WHERE q.product         = 'bread'
     GROUP BY q.customer_id
    HAVING SUM(q.qty)        >= 5
  )

There are some other approaches. You specifically asked about changing "rows" into "columns". The term we usually use to refer to that type of operation is "pivot" or "pivot table".

Here's an example of that approach:

SELECT t.customer_id
     , SUM(IF(t.product= 'cheese'  ,t.qty,NULL)) AS `qty_cheese`
     , SUM(IF(t.product= 'bread'   ,t.qty,NULL)) AS `qty_bread`
  FROM mytable t
 GROUP BY t.customer_id
HAVING `qty_cheese`  >= 2
    OR `qty_bread`   >= 5

Again, this is checking the "total" qty for a given customer and given product. If you just want to check for the existence of a row that has qty >= n, then replace the SUM() aggregate function with MAX().

For each row returned from mytable, we're evaluating an expression, comparing product to a specific value, and if it matches, we're returning the value from the qty column.

We typically do this in the SELECT list, just because it allows us to debug the query, we can leave off the "HAVING" clause, and get the list of all customers, and see the qty for each product.

It's not necessary that these expressions be in the SELECT list. These expressions could be moved to the HAVING clause.

SELECT t.customer_id
  FROM mytable t
 GROUP BY t.customer_id
HAVING SUM(IF(t.product= 'cheese'  ,t.qty,NULL))  >= 2
    OR SUM(IF(t.product= 'bread'   ,t.qty,NULL))  >= 5

There are a couple of other viable approaches.

Upvotes: 1

Mihai
Mihai

Reputation: 26784

SELECT t.customerID,
 SUM(CASE WHEN t.product='cheese' THEN quantity ELSE 0 END) as cheese,
 SUM(CASE WHEN t.product='bread' THEN quantity ELSE 0 END) as bread
FROM t
GROUP BY t.customerID
HAVING cheese>=2
OR bread>=5;

FIDDLE

Or move them to the HAVING clause for clarity

FIDDLE

Upvotes: 2

user4089220
user4089220

Reputation: 

select distinct customerID from Products
where (product = 'cheese' and qty >= 2) or (product = 'bread' and qty >= 5);

Upvotes: 2

Related Questions