0

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.

Krikey
  • 19
  • 3
  • See #5 and #3 [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question) and update the qestion – Ergest Basha Aug 23 '23 at 09:31
  • edited to explain the table structure and what I would like the output to look like. – Krikey Aug 24 '23 at 16:48

2 Answers2

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>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
nacho
  • 5,280
  • 2
  • 25
  • 34
  • Ive modified my original question to provide an example of what Id like the output to look like. The cost_complete column would be false if any of the product 'cost' fields were 0 or NULL. – Krikey Aug 24 '23 at 16:29
  • I have modified my answer to fulfill your comment requeriments – nacho Aug 25 '23 at 14:10
  • This seems to return a '1' for the min(case when p.cost is null then false else true end) part no matter whether each p.cost is 0, NULL or has a positive value – Krikey Aug 25 '23 at 15:36
  • Why use a single-option CASE instead of IF? – UncleCarl Aug 28 '23 at 16:07
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
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.

Christian
  • 553
  • 4
  • 16