SUM syntax - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SUM syntax

Author

Topic

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-02-15 : 09:43:28

What is the syntax for selecting a column as sum, but subtracting the first sum value in the ascending order.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-15 : 13:26:21

SELECT SUM(Col1) - MIN(Col1)
FROM Table1

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-02-16 : 10:52:50

This is actually good info because I didn't know how to subtract the SMALLEST value of a SUM, so thanks.

However, what I'm looking to find out is how to select a column, order the column ascending based on a date column that I have, then subtract the first value in the order. Any help on this would be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-16 : 10:58:34

There is no such thing as order in a relational table, unless you specify the order when selecting, with ORDER BY.
Please define what "first" is for you.

E 12°55'05.63"
N 56°04'39.26"

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-02-16 : 11:13:17

Below is the sql sever code I'm trying to execute(currently incorrect but it may give a better idea of what I'm trying to do). SPK_Area contains integers I'm trying to add. I'm trying to select all integers in the SPK_Area column that meet the conditions of the parameter input, use ORDER BY StartDateTime to put SPK_Area integers in order, then add them excluding the first one in the order.

SELECT SUM(SPK_Area) - SUM(SPK_Area[1]) AS SPKSum
FROM z_BWD_BottleVariance
WHERE (Line = @Line) AND (PO = @PO) AND (Bottle_SKU = @Bottle_SKU) AND (StartDateTime>=@StartDateTime) AND (EndDateTime<=@EndDateTime)
GROUP BY PO, Bottle_SKU
ORDER BY StartDateTime ASC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-16 : 11:22:28

[code]SELECT PO,
Bottle_SKU,
SUM(CASE WHEN recID = 1 THEN -SPK_Area ELSE SPK_Area END)
FROM (
SELECT PO,
Bottle_SKU,
SPK_Area,
ROW_NUMBER() OVER (PARTITION BY PO, Bottle_SKU ORDER BY StartDateTime) AS recID
FROM z_BWD_BottleVariance
WHERE Line = @Line
AND PO = @PO
AND Bottle_SKU = @Bottle_SKU
AND StartDateTime >= @StartDateTime
AND EndDateTime <= @EndDateTime
) AS d
GROUP BY PO,
Bottle_SKU[/code]

E 12°55'05.63"
N 56°04'39.26"

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-02-16 : 13:00:03

Thank you for your replies. I did not use the snippet of code you gave, however I did find an answer to the problem I was trying to solve and I've posted the code below just in case anyone is interested. What I did was configure the WHERE clause to do a sub query so the MIN(StartDateTime) was excluded.

SELECT SUM(SPK_Area) AS SPKSum
FROM z_BWD_BottleVariance
WHERE (Line = @Line) AND (PO = @PO) AND (Bottle_SKU = @Bottle_SKU) AND (StartDateTime>=@StartDateTime) AND (EndDateTime<=@EndDateTime) AND StartDateTime <> (SELECT MIN(StartDateTime)
FROM z_BWD_BottleVariance
WHERE (Line = @Line) AND (PO = @PO) AND (Bottle_SKU = @Bottle_SKU) AND (StartDateTime>=@StartDateTime) AND (EndDateTime<=@EndDateTime) )
GROUP BY PO, Bottle_SKU

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 13:01:26

Did you try with Peso's code? His code looks efficient than yours.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-16 : 13:02:23

Great!
Compare both queries and see which is faster and use less resources.
Let us know the result.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources