SQL 2005 Help! - 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)

SQL 2005 Help!

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2010-08-26 : 14:35:40

Folks:

Here is a scenario of data I have.

quote:
DECLARE @Numbers TABLE
(
ID varchar(10), Marks1 smallint, Marks2 smallint
)

INSERT @Numbers
SELECT 'S1', 6, 3 UNION ALL
SELECT 'S2', 4, 8 UNION ALL
SELECT 'S3', 2, 6

SELECT * FROM @Numbers

I want the total for 'Marks 1 + Marks2' for each ID. How will be my SQL Query be?

Output Should be:

ID Marks1 Marks2 TOTAL
S1 6 3 9
S2 4 8 12
S3 2 6 8

Thanks !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-26 : 14:48:19

SELECT ID, Marks1, Marks2, Marks1 + Marks2 AS TOTAL
FROM @Numbers

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqldba20
Posting Yak Master

183 Posts

Posted - 2010-08-26 : 15:09:14

My requirement is to get Total of Marks1, Total of Marks 2 and I use the WITH ROLLUP command to get the Total. But if I use WITH Rollup then I don't get Marks1+Marks2. Here is the query I use:

SELECT ID,SUM(Marks1) Marks1,SUM(Marks2) Marks2,(Marks1+Marks2) AS Total FROM @Numbers
GROUP BY ID
WITH ROLLUP

The Output should be:

ID Marks1 Marks2 TOTAL
S1 6 3 9
S2 4 8 12
S3 2 6 8
TOTAL 12 17 29

quote:
Originally posted by tkizer

SELECT ID, Marks1, Marks2, Marks1 + Marks2 AS TOTAL
FROM @Numbers

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqldba20
Posting Yak Master

183 Posts

Posted - 2010-08-26 : 15:09:41

My Apologies... I should have been more clear.... My requirement is to get Total of Marks1, Total of Marks 2 and I use the WITH ROLLUP command to get the Total. If I use WITH Rollup then I don't get Marks1+Marks2. Here is the query I use:

SELECT ID,SUM(Marks1) Marks1,SUM(Marks2) Marks2,(Marks1+Marks2) AS Total FROM @Numbers
GROUP BY ID
WITH ROLLUP

The Output should be:

ID Marks1 Marks2 TOTAL
S1 6 3 9
S2 4 8 12
S3 2 6 8
TOTAL 12 17 29

quote:
Originally posted by tkizer

SELECT ID, Marks1, Marks2, Marks1 + Marks2 AS TOTAL
FROM @Numbers

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

[/quote]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-26 : 16:56:11

SELECT ID,SUM(Marks1) Marks1,SUM(Marks2) Marks2, SUM(Marks1+Marks2) AS Total FROM @Numbers
GROUP BY ID
WITH ROLLUP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources