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.
| Author |
Topic |
|
cpoxland
Starting Member
2 Posts |
 Posted - 2010-06-16 : 04:56:24
|
Hi,I'm having a problem summarizing data for use in a sales managing application. I have three tables, though only two are relevant to my problems.The first table is "Items" which for every row contains a unique new item with an ID and a price.The second table is "Sales" and it contains (most importantly) an item ID (linking it to items), the quantity of items, and a sell time.I want to select the Sales rows and output them grouped by sell time. That part I have working splendidly. However, I also want to show the total cost of all items, summarized. So if two rows of Items look like this:ID = 1, Name = Sunglasses, Price = 5,99ID = 2, Name = Scissors, Price = 2,99And two sales rows look like this:Item ID = 1, Quantity = 2, Sell time = 2010-06-16 10:30Item ID = 2, Quantity = 3, Sell time = 2010-06-16 10:30Then I want the grouped (by sell time) Sales rows to have the a "Column" called "Total Price" which should equal:(3 * Scissors price) + (2 * Sunglasses price) = 2 * 5,99 + 3 * 2,99This is the code I'm using, why is is wrong, how do I make it work?SELECT [Sales].[SellTime], SUM(SELECT ([Items].[Price] * [Sales].[Quantity]) FROM [Items] WHERE [Items].[Id] = [Sales].[ItemId]) AS [TotalPrice]FROM [Sales]GROUP BY [Sales].[SellTime] The emphasized lines cause the failure. |
|
|
matty
Posting Yak Master
161 Posts |
Posted - 2010-06-16 : 05:42:50
|
| [code]SELECT s.SellTime, SUM(i.Price * s.Quantity) AS [TotalPrice]FROM Items iJOIn Sales s ON s.ItemId = i.IdGROUP BY SellTime[/code] |
 |
|
|
cpoxland
Starting Member
2 Posts |
Posted - 2010-06-16 : 06:42:55
|
| Dude, thanks!That does it. |
|
|
|
|
|
|
|
|
Summarize prices and quantities