CODEWITHSUNDEEP
sqlsql-server
Revokez
Revokez

Reputation: 323

Calculating the sum of rows that have been grouped in SQL

I have the query:

SELECT substring(Code,1,6) as Code
From Table
Group by substring(Code,1,6)
Order By Code

However I'm trying to add a column that is the calculated sum of the rows that have been grouped in this query. Example:

Code    Number

S05080A 20

S05080B 20

S70331A 8

S70331B 4

Would then become:

Code    Sum

S05080  40

S70331  12

Hopefully someone can help me out with this one, would be much appreciated!

Upvotes: 0

Views: 60

Answers (5)

mohan111
mohan111

Reputation: 8865

declare @t table (Code varchar(10),number int)
insert into @t(code,number)values  ('S05080A',20), ('S05080B',20), ('S70331A',8),('S70331B', 4)

SELECT  SUBSTRING(Code, 1, NULLIF(DATALENGTH(Code)-1,-1)),SUM(number)
From @t
Group by SUBSTRING(Code, 1, NULLIF(DATALENGTH(Code)-1,-1))
Order By SUBSTRING(Code, 1, NULLIF(DATALENGTH(Code)-1,-1))

Upvotes: 0

Ionic
Ionic

Reputation: 3935

You can use this:

CREATE TABLE #codes(code nvarchar(10), number int)

INSERT INTO #codes(code, number)
VALUES(N'S05080A', 20), (N'S05080B', 20), (N'S70331A', 8), (N'S70331B', 4)

-- This is your part
SELECT LEFT(code,6) as code,SUM(number)
FROM #codes
GROUP BY LEFT(code,6)

DROP TABLE #codes

Upvotes: 1

Bla...
Bla...

Reputation: 7288

You can use SUM, like below:

SELECT substring(Code,1,6) as Code, SUM(Number) as Sum
From Table
Group by substring(Code,1,6)
Order By Code

Upvotes: 1

Jamiec
Jamiec

Reputation: 136074

You've already done the hard work - simply add SUM(Number) as another column and alias it

SELECT substring(Code,1,6) as Code, SUM(Number) AS Sum
From Table
Group by substring(Code,1,6)
Order By Code

Upvotes: 1

Sergei Yevlakov
Sergei Yevlakov

Reputation: 66

SELECT substring(Code,1,6) as Code,
       SUM(Number) AS [Sum] -- Add this line

From Table

Group by substring(Code,1,6)

Order By Code

Upvotes: 3

Related Questions