CODEWITHSUNDEEP
mysql
Pedro Bernardo
Pedro Bernardo

Reputation: 49

Selecting several rows from a column

Below is the order table

order_table     
product_id  amount  price
1           2       4
1           5       4
2           2       4
2           3       4
3           4       4
4           3       4
4           2       4
4           2       4
5           4       4
5           1       4
6           1       4
6           1       4

And below is my values table

value_table 
product_id  value
1           a
1           b
1           c
2           a
2           b
2           c
3           a
3           d
3           c
4           a
4           d
4           c
5           a
5           b
5           c
6           a
6           d
6           c

What I want to achieve is the table below.

product_id  amount*price as total values
3           12                    a,c and not equal to b
4           28                    a,c and not equal to b
6           8                     a,c and not equal to b

I do understand that once you group the product_id then it will only show you a value ='a' or 'c'. and that's fine what I am looking for is the products that contain both a,c but don't want a product_id that has a value equal to = b So to summarize only 3,4,6 should appear grouped so that I don't sum up duplicates of the same order.

Upvotes: 1

Views: 47

Answers (1)

Raging Bull
Raging Bull

Reputation: 18767

Try this:

SELECT product_id,SUM(amount*price) as Totalprice
FROM order_table
WHERE product_id NOT IN
      (SELECT product_id
       FROM value_table
       WHERE value='b')
GROUP BY product_id

Explanation:

The inner query selects a list of product_ids which have b as value. So inner query will have a result of (1,2,5).

The outer query calculates the sum of prices of product_ids which are not present in that list. i.e., Not in the list of (1,2,5)

Result:

PRODUCT_ID  TOTAL
3           16
4           28
6           8

See result in SQL Fiddle.

Upvotes: 1

Related Questions