joining and grouping by

[generic]

I have to tables, products and manufacturers, that looks basically like this:

PRODUCTS
|- prod_id
|- prod_name
|- prod_qty

MANUFACTURERS
|- man_id
|- man_name

What I'm trying to do is output a list of available stock, by manufacturer, so something like:

"Manufacturer: $man_name | SUM($prod_qty) in stock"

So I'm trying to get the total sum of prod_qty (in stock product) for each associated prod_man (manufacturer id).

I'd like to show you what I have currently, but it's a gnarley nest of loops and queries that really does nothing but spout errors. Can anyone show me how to write a query like this?

Thanks in advance!

[coopster]

You'll need to join the two tables. But I do not see any common keys shared between the two tables to join them?

[generic]

The prod_man field holds the id of the manufacturer (man_id) but that's as common as it gets. I just can't wrap my head around joins at all, no matter how many books or tutorials I look at.

[coopster]

So if a manufacturer has 10 different products, you only want one line to show with the total quantity of all 10 different products summed up?

[generic]

Yea, it's basically just so I can tally total amount of stock by manufacturer. So if there are two manufacturers, Acme and WidgetCo, I'd like it loop through and return the total sum of prod_qty where prod_man = man_id

Acme: 49 unique products (246 items in stock)
WidgetCo: 26 unique products (421 items in stock)

Or something like that. We're tracking t-shirt transfers so we don't want to enter each individual transfer (and amount of each transfer in stock) as inventory, we'd rather just keep track of how many transfers we have, in total, by manufacturer.

[coopster]

Understood. It would looks something like this ...

SELECT 
  m.man_name AS Manufacturer, 
  SUM(prod_qty) AS Qty 
FROM manufacturers AS m 
INNER JOIN products AS p ON (m.man_id = p.prod_man) 
GROUP BY m.man_name 
ORDER BY m.man_name 
;

You select from your primary table which is the manufacturers and join the products table on the primary key, manufacturer identifier. In order to SUM you need to use a GROUP BY.

[generic]

That's amazing. I just can't seem to wrap my head around joins and my code is about four times longer and uglier than it needs to be because of that. That is exactly what I needed. Thank you so much coopster, I really appreciate your help!

[skoff]

you should read about inner join and all that stuff it would really help your query to run faster and try to avoid using select inside a select method this is hard on the server! Coopster got it right

[generic]

I completely agree. I know I needed to use joins but I just can't wrap my head around them no matter how many times I try. I'm tenacious though, one of these days the idea will stick. Cheers.