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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-12 : 08:52:42
|
| Mike writes "Hi,I need a solution on this!i have a table which represents all orders on a product. Every order has a actionnumber. So when i order a product, the date and the actionnumber, the price and the productID is inserted and many other fields are inserted.Now it could happen that a product is ordered, then canceled, then ordered again. So the actionumber on this product is the same.Looks like thisAction = 7500, Price 2500,Date 01/01/02Action = 7500, Price 0,Date 01/01/02Action = 7500, Price 2500,Date 01/01/02Action = 7501, Price 20,Date 01/01/02Now i want to do a report which shows me the sum of the first booking on every actionnumber of each dayShould then look like thisDate 01/01/02 Sum 2520Can you help me on thisThanks" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-12 : 09:04:22
|
| You can get partway there by using SUM(DISTINCT):SELECT [Date], Sum(DISTINCT Price) FROM myTable GROUP BY [Date]Each distinct value (0, 2500) will only be summed ONCE, no matter how many rows have that value.The problem with this is that it won't sum 2 DIFFERENT action numbers if the price is the same (if 7501 had a price of 2500, it wouldn't be summed). I don't know if that's an issue for you. You'd have to use a subquery to find the distinct action numbers, prices, and dates, and then sum the subquery. |
 |
|
|
|
|
|
|
|
SUM Function