CODEWITHSUNDEEP
mysqlif-statementleft-joinifnull
Krikey
Krikey

Reputation: 19

Query that sets a flag based on a condition for a group of left joined records

I have 2 tables, 'manufacturers' and 'products'. A manufacturer can have multiple products and a product has a 'qty', 'price' and 'cost' field.

Manufacturers table

manufacturer_id name
1 ABC
2 Meteor
3 XYZ
4 Airfix

Products table

product_id manufacturer_id price cost qty
100 1 123.32 32.23 32
101 2 123.32 32.23 23
102 3 123.32 32.23 16
103 2 123.32 32.23 8
104 1 123.32 12.45 5
105 3 123.32 0.00 3
106 3 123.32 32.23 99
107 4 123.32 32.23 88

I've LEFT JOINed them together and can get SUMs on the 'price' and 'cost' fields based on multiplying by 'qty' but I'd like to get a 'cost_complete' flag set to false if ANY of the product.cost fields from a specific manufacturer are not set, thus allowing me to identify any manufacturer who has products that don't yet have a 'cost' set.

This is what I have so far, but I think that my 'cost_complete' flag gets overwritten for each product.

SELECT m.name, p.quantity, p.price, p.cost, 
SUM(p.quantity) AS total_quantity,
SUM(p.price*p.quantity) AS total_price,
SUM(p.cost*p.quantity) AS total_cost, IF(p.cost>0, 'true','false') AS cost_complete
FROM manufacturer m LEFT JOIN product p ON m.manufacturer_id = p.manufacturer_id GROUP BY m.manufacturer_id ORDER by m.name

Modified to explain what I am expecting:

name quantity total_price total_cost cost_complete
ABC 56 £345.23 £123.32 true
Meteor 12 £1345.23 £23.32 true
XYZ 123 £345.23 £65.12 false
Airfix 66 £445.23 £12.12 true

...where the cost_complete column is set to false when not all of the cost fields have a value greater than 0.

Upvotes: 0

Views: 72

Answers (2)

Christian
Christian

Reputation: 831

To query a list of manufacturers who have at least one product that has no cost set, this query should do.

SELECT
    m.name,
    SUM(p.quantity) AS total_quantity,
    SUM(p.price * p.quantity) AS total_price,
    SUM(p.cost * p.quantity) AS total_cost,
    MIN(IFNULL(p.cost, 0)) AS min_cost            # 👈 see no. 2.4
FROM manufacturer m
    LEFT JOIN product p ON m.manufacturer_id = p.manufacturer_id
GROUP BY m.manufacturer_id
HAVING min_cost = 0                               # 👈 see no. 3
ORDER BY m.name;

I'd like to imagine what this query does as follows:

  1. For each manufacturer, get the list of products it manufactured.
  2. For each list of products in step 1, go through each product and do the following:
    1. aggregate the quantity (as total_quantity),
    2. aggregate the price (as total_price),
    3. aggregate the total cost (as total_cost), and
    4. note the minimum cost among all products sifted through (as min_cost).
  3. Only filter in manufacturers whose minimum product cost (min_cost) is zero.
  4. Output manufacturer info, as well as the aggregated info gathered in step 2.
  5. Sort step 4 by manufacturer name.

Notice how I removed p.quantity, p.price, and p.cost from the SELECT statement. This is because we used the SELECT statement to aggregate a summary about a list of products under a manufacturer; there's no picking a single product. In short, it won't make sense to include them anymore as there could be more than one product under the same manufacturer that have no cost set.

Also, as noted in the comments, I'm using the alias named min_cost in the HAVING clause. This is a MySQL extension to the standard SQL, making the syntax valid by default, unless sql_mode is set to ONLY_FULL_GROUP_BY.

Upvotes: 0

nacho
nacho

Reputation: 5397

You can do a conditional sum in case the p.cost is null (or is lower than 0 as you have in your code). Then you will have the number of products that don´t have a p.cost (if it is 0, all products have a cost)

SELECT m.name, p.quantity, p.price, p.cost, SUM(p.quantity) AS total_quantity, 
    SUM(p.price*p.quantity) AS total_price, SUM(p.cost*p.quantity) AS total_cost, 
    sum(case when p.cost>0 then 1 else 0 end) 
FROM manufacturer m 
LEFT JOIN product p ON m.manufacturer_id = p.manufacturer_id 
GROUP BY m.manufacturer_id 
ORDER by m.name

P.D. To answer your comment, you can do it this way:

SELECT m.name, p.quantity, p.price, p.cost, SUM(p.quantity) AS total_quantity, 
    SUM(p.price*p.quantity) AS total_price, SUM(p.cost*p.quantity) AS total_cost, 
    min(case when p.cost>0 then true else false end) 
FROM manufacturer m 
LEFT JOIN product p ON m.manufacturer_id = p.manufacturer_id 
GROUP BY m.manufacturer_id 
ORDER by m.name

Upvotes: 0

Related Questions