Query issue with totaling and grouping

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

Query issue with totaling and grouping

Author

Topic

Hammerklavier
Starting Member

26 Posts

Posted - 2014-01-09 : 15:45:47

Hi,

For part of my query, I'm trying to pull a single total, but I can't figure out how to do this. Here is a sample table:

OrderID-----OrderAmount----ItemID
001---------3.50-----------ABB
002---------1.00-----------CDED
002---------1.00-----------FHG
002---------1.00-----------LMN
003---------2.50-----------ZYZ

Here is a table showing orders and the items on each order. The items are not relevant to my query; I've only included them here to illustrate why a particular OrderID and its corresponding OrderAmount can occupy multiple rows.

I'm trying to find a way to add up the total OrderAmount for these three orders with a single value. So in this example, I'm looking for $7.00. When I try using SUM(OrderAmount), it treats each OrderAmount from OrderID #2 as a separate value even though its really just one value. So I end up with $9.00.

I'd be grateful for any suggestions

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-09 : 16:39:57

Without your query, it's difficult to guide you, so my suggestions are just guess. One of these suggestions might work for you:
- if possible, sum order item amount
- don't join the item table
- use destinct
- group by orderid, and use avg on order amount. then use as sub-query and sum the avg value

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 05:09:48

this is a workaround you can use.


SELECT SUM(CASE WHEN Seq=1 THEN OrderAmount ELSE 0 END) AS TotalOrderAmount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY ItemID ) AS Seq,*
FROM Table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources