Welcome to WebmasterWorld Guest from 54.242.224.250

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

joining and grouping by

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

Junior Member

10+ Year Member

joined:Apr 24, 2004
posts: 127
votes: 0


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)

Administrator

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

joined:July 31, 2003
posts:12541
votes: 1


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)

Junior Member

10+ Year Member

joined:Apr 24, 2004
posts: 127
votes: 0


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)

Administrator

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

joined:July 31, 2003
posts:12541
votes: 1


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)

Junior Member

10+ Year Member

joined:Apr 24, 2004
posts: 127
votes: 0


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)

Administrator

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

joined:July 31, 2003
posts:12541
votes: 1


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)

Junior Member

10+ Year Member

joined:Apr 24, 2004
posts: 127
votes: 0


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)

Junior Member

5+ Year Member

joined:Nov 16, 2008
posts: 136
votes: 0


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)

Junior Member

10+ Year Member

joined:Apr 24, 2004
posts: 127
votes: 0


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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members