Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sum Query per other Column Value

Author  Topic 

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2013-02-11 : 16:22:35
I have the following query which is almost what I need.
SELECT     porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal, 
SUM(items.Price) AS SumOfLabor, Null as SumOfMaterials, items.Type
FROM porders INNER JOIN
items ON porders.PONum = items.PONum INNER JOIN
vendors ON porders.VendorID = vendors.VendorID INNER JOIN
postatus ON porders.StatusID = postatus.StatusID
WHERE (vendors.VendorName = @SearchValue)
GROUP BY porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal,
items.Type
HAVING (items.Type = 'L') AND (porders.Date BETWEEN @StartDate AND @StopDate)
UNION ALL
SELECT porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal,
Null as SumOfLabor, SUM(items.Price) AS SumOfMaterials, items.Type
FROM porders INNER JOIN
items ON porders.PONum = items.PONum INNER JOIN
vendors ON porders.VendorID = vendors.VendorID INNER JOIN
postatus ON porders.StatusID = postatus.StatusID
WHERE (vendors.VendorName = @SearchValue)
GROUP BY porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal,
items.Type
HAVING (items.Type = 'M') AND (porders.Date BETWEEN @StartDate AND @StopDate)
ORDER BY PONum


I have POrders-(Parent) and Items-(Child) tables. In the items table when they enter an ordered item they choose whether it is L(Labor) or M(Materials). I am trying to do a query where the user can enter a Vendor and Start & Stop Dates and then Sum the items.Price column for each Order. So it would have PO# and then SumOfLabor and SumOfMaterials. My query above will return the sum of those columns but it puts each as it's own record. Is there a way to have it fill in the SumOfMaterials if that PO already exists (from the SumOfLabor query) and if it doesn't then it would enter a new record for that PO?

This is what it returns...
PONum VendID MPONum VendorName Date Status Tax Freight POTotal SumOfLabor SumOfMaterials Type
12307 386 M12307 Dahlke Trailer Sales 2013-01-07 Open NULL NULL 517.68 210 NULL L
12307 386 M12307 Dahlke Trailer Sales 2013-01-07 Open NULL NULL 517.68 NULL 307.68000030517 M

I want...
PONum VendID MPONum VendorName Date Status Tax Freight POTotal SumOfLabor SumOfMaterials Type
12307 386 M12307 Dahlke Trailer Sales 2013-01-07 Open NULL NULL 517.68 210 307.68000030517 M

I don't need the Type field - was just using that for testing.

Thanks for your help.
Stacy

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-02-11 : 18:41:47
[CODE]sum(case when items.Type = 'L' then items.Price else 0.0 end) as SumOfLabor,
sum(case when items.Type = 'M' then items.Price else 0.0 end) as SumOfMaterial,[/CODE]

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2013-02-12 : 11:11:45
quote:
Originally posted by Bustaz Kool

[CODE]sum(case when items.Type = 'L' then items.Price else 0.0 end) as SumOfLabor,
sum(case when items.Type = 'M' then items.Price else 0.0 end) as SumOfMaterial,[/CODE]

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber



I got it! Thanks so much. Learn something new everyday. This is what I ended up with and it is perfect!
SELECT     porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal, 
sum(case when items.Type = 'L' then items.Price else 0.0 end) as SumOfLabor, sum(case when items.Type = 'M' then items.Price else 0.0 end) as SumOfMaterial
FROM porders INNER JOIN
items ON porders.PONum = items.PONum INNER JOIN
vendors ON porders.VendorID = vendors.VendorID INNER JOIN
postatus ON porders.StatusID = postatus.StatusID
WHERE (vendors.VendorName = 'Dahlke Trailer Sales')
GROUP BY porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal
HAVING (porders.Date BETWEEN '01/01/2013' AND '12/31/2013')


Thanks again!
Stacy
Go to Top of Page
   

- Advertisement -