Question 236169 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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.

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
Tags:

2

Answers


  1. 0

    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 is null 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
    Login or Signup to reply.
  2. 0

    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
HAVING min_cost = 0                               # 👈 see no. 3
GROUP BY m.manufacturer_id
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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search