Welcome to WebmasterWorld Guest from 54.166.224.46

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

joining and grouping by

     
7:30 pm on Jan 8, 2013 (gmt 0)

10+ Year Member



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!
3:02 pm on Jan 9, 2013 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



You'll need to join the two tables. But I do not see any common keys shared between the two tables to join them?
3:24 pm on Jan 9, 2013 (gmt 0)

10+ Year Member



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.
3:31 pm on Jan 9, 2013 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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?
3:42 pm on Jan 9, 2013 (gmt 0)

10+ Year Member



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.
3:45 pm on Jan 9, 2013 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
4:00 pm on Jan 9, 2013 (gmt 0)

10+ Year Member



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!
9:08 pm on Jan 9, 2013 (gmt 0)

5+ Year Member



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
9:12 pm on Jan 9, 2013 (gmt 0)

10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month