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

Question

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.

Answer 1

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

Answer 2

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.