How to sum rows

Question

Post reply

How to sum rows

steven_nguyenhuy

SSC Veteran

Points: 221
More actions
June 29, 2010 at 10:05 pm

#220647

I have a table as below
orderid buyerid title saleprice partnum
123 duc Axiom69 100 Axiom69-a
123 duc Native 50 Nat-a
345 tracy Keyboard69 100 Key69-a
678 tracy Keyboard69 100 Key69-a
910 Thuan remote 10 remote-a
The result should be below so can anyone help to provide me a SQL Query to extract the data as below, thank you
orderid buyerid title sum(saleprice) partnum
123 duc Axiom69 100 Axiom69-a
123 duc Native 50 Nat-a
150
345 tracy Keyboard69 100 Key69-a
678 tracy Keyboard69 100 Key69-a
200
910 Thuan remote 10 remote-a

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

Try using GROUPING SETS ,ROLLUP or CUBE with group by function.

http://msdn.microsoft.com/en-us/library/ms177673.aspx

"More Green More Oxygen !! Plant a tree today"

parthenitin Ten Centuries Points: 1190 More actions · Answer 2

Try the follwoing query......

select case convert(varchar(20),orderid) when 0 then 'Total' else convert(varchar(20),orderid) end as Orderid,

buyerid,saleprice,title

from

(

select * from (

select case grouping(title) when 1 then 0 else orderid end as orderid,buyerid,sum(saleprice) as saleprice,

partnum,grouping(title) as title

from test1

where buyerid is not null

group by buyerid,title,orderid

with rollup

--order by 1

)

as final where orderid is not null and buyerid is not null

) RightTable

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

What is the result set used for, Steven?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Steven, can you please have a look at formatting your data and requested output into something we can read.

I think your source data should look like this: -

DECLARE @sourcetable AS TABLE(

orderid INT

,buyerid VARCHAR(15)

,title VARCHAR(35)

,saleprice INT

,partnum VARCHAR(35))

INSERT INTO @sourcetable

(orderid

,buyerid

,title

,saleprice

,partnum)

SELECT 123, 'duc', 'Axiom69', 100, 'Axiom69-a'

UNION ALL SELECT 123, 'duc', 'Native', 50, 'Nat-a'

UNION ALL SELECT 345, 'tracy', 'Keyboard69', 100, 'Key69-a'

UNION ALL SELECT 678, 'tracy', 'Keyboard69', 100, 'Key69-a'

UNION ALL SELECT 910, 'Thuan', 'remote', 10, 'remote-a'

--SELECT * FROM @sourcetable

/*

orderid buyerid title saleprice partnum

----------- --------------- ----------------------------------- ----------- -----------------------------------

123 duc Axiom69 100 Axiom69-a

123 duc Native 50 Nat-a

345 tracy Keyboard69 100 Key69-a

678 tracy Keyboard69 100 Key69-a

910 Thuan remote 10 remote-a

*/

After that, I have little idea what you're after.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

Minaz Amin (6/29/2010)

Try using GROUPING SETS ,ROLLUP or CUBE with group by function.

http://msdn.microsoft.com/en-us/library/ms177673.aspx%5B/quote%5D

Agreed and Spot On.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Ashwani Malik Ten Centuries Points: 1090 More actions · Answer 6

SELECT orderid,buyerid,title,SUM(saleprice),partnum FROM @sourcetable Group by

Grouping sets ((orderid,buyerid,title,partnum ),(orderid),())