Select S.styleName, C.ColorName,
Sum(Case when SZ.SizeName = 'S' then IL.qtyOnHand else 0 end) S,
Sum(Case when SZ.SizeName = 'M' then IL.qtyOnHand else 0 end) M,
Sum(Case when SZ.SizeName = 'L' then IL.qtyOnHand else 0 end) L,
Sum(Case when SZ.SizeName = 'XL' then IL.qtyOnHand else 0 end)
Xl,
>From (your inner joins here)
Group by S.StyleName,C.ColorName
This should take care of what you want.
Christian
-----Original Message-----
From: Kevin Rosenthal [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 05, 2005 8:00 PM
To: SQL
Subject: Question on Getting sums in a query
Greetings,
I have a situation that I can not seem to figure out. I have a table
that has information on an inventory. The table has the following
information:
tblInventoryList
styleID - links to style description and name {tblStyles (styleID,
styleName)} colorID - links to color name and number {tblColors
(colorID, colorName)} sizeID - links to Size {tblSizes (sizeID,
sizeName)} qtyOhHand - current count for this style, color and size
dateUpdated - date of last update to count
OK, I can join the tables to get the names in place of the ID numbers,
but what I want to do is show the listing with a count by size and color
for each style and a total for the style. See below....
Style: 11792
S M L XL
Red: 36 72 72 36
Blue: 24 48 48 24
---------------------------
Total:60 120 120 60
Now, how would I code this in SQL? I tried group by style, color, size,
but I got all kinds of funky results. :(
Help please!
Thanks,
Kevin
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.12/46 - Release Date: 7/11/2005
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2329
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
