sum of columns in sql

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

I have a table like this.

C#

Sno  |  Sname  |  Marks  
001  |  abcde  |  90
002  |  fghij  |  80
003  |  lmnop  |  70
004  |  qrstu  |  60
005  |  vwxyz  |  50

I want to make a total like this

C#

Sno  |  Sname  |  Marks     |  Total  
001  |  abcde  |  90        |  -
002  |  fghij  |  80        |  -
003  |  lmnop  |  70        |  -
004  |  qrstu  |  60        |  -
005  |  vwxyz  |  50        |  350

Please help in this...

Thanks in advance...

Posted 17-Sep-13 20:52pm

nsvrao

Updated 17-Sep-13 21:24pm

Thanks7872

v3

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad_Kulkarni · Answer 1 · 2013-09-17T20:58:00

Solution 1

Something like this:

SQL

SELECT 
sub1,
sub2,
sub3,
(sub1 + sub2 + sub3) as 'Total Marks'
FROM Student

Posted 17-Sep-13 20:58pm

Prasad_Kulkarni

Saral S Stalin · Answer 2 · 2013-09-17T22:57:00

Solution 3

Try below snippet. It uses ROLLUP feature.

SQL

DECLARE @Table Table (Sno VARCHAR(3) , Sname VARCHAR(20),  Marks  INT)
INSERT INTO @Table VALUES ('001'  ,  'abcde'  ,  90)
,('002'  ,  'fghij'  ,  80)
,('003'  ,  'lmnop'  ,  70)
,('004'  ,  'qrstu'  ,  60)
,('005'  ,  'vwxyz'  ,  50)

SELECT Sno, SUM(Marks) Marks
FROM @Table
GROUP BY Sno WITH ROLLUP

This will give you output like

Sno  Marks
---- -----------
001  90
002  80
003  70
004  60
005  50
NULL 350

Posted 17-Sep-13 22:57pm

Saral S Stalin

Comments

nsvrao 18-Sep-13 5:37am

thanks for your solution... but according to the requirement, I am not supposed to declare another table please give me a query with in the select command it self... if possible

Saral S Stalin 18-Sep-13 6:38am

Dear, I just gave you a hint about ROLLUP feature. You do not need to declare another table, you can use this against your existing table only. But I will not be doing the coding for you.

gauravupadhyay 18-Sep-13 7:11am

rollup will not give him desired result what he wants is
Sno | Sname | Marks | Total
001 | abcde | 90 | -
002 | fghij | 80 | -
003 | lmnop | 70 | -
004 | qrstu | 60 | -
005 | vwxyz | 50 | 350

while rollup add one new row to the table .

gauravupadhyay · Answer 3 · 2013-09-18T02:26:00

Solution 4

Hi nsvrao please use this sql script i tested it and its result as u desire .

SQL

SELECT
sno ,Sname , marks ,
'totalmarks'=
 CASE
 WHEN convert(int , sno)   = ( SELECT count(*) FROM tbl_test)
 THEN
   CONVERT(varchar , (SELECT sum(marks) FROM tbl_test))
   ELSE
   '-'
   END
  FROM tbl_test

i am working on simplification of this select query if possible

regards

Posted 18-Sep-13 2:26am

gauravupadhyay

Updated 18-Sep-13 2:27am

v2

Comments

Raja Sekhar S 20-Sep-13 5:01am

In your Query for each and every value the select count(*) will be executed... if there are 10 records in that table... select count(*) will run for 10 times... and if the sno is not Order wise then also some issues will arise...

Raja Sekhar S · Answer 4 · 2013-09-19T22:57:00

SQL

Create Table #Temp
	(
	 sno Int,
	 Sname varchar(40),
	 Marks Int
	)

Insert into #Temp 
Values(001,'abcde',90),(002,'fghij',80),(003,'lmnop',70),(004,'qrstu',60),(005,'vwxyz',50)

;with cte
as
(
Select sno,Sname,Marks,ROW_NUMBER() Over(Order by Sno) rno From #Temp
)

Select st.sno,st.Sname,st.Marks,COALESCE(CONVERT(VARCHAR,t.Total),'--') [Total]  from cte st
left outer join (Select sum(Marks) [Total],max(rno) [rno] From cte ) t
on t.rno=st.rno

Drop Table #Temp

Output:

SQL

sno	Sname	Marks	Total
---    ------- ----- --------
1	abcde	90	--
2	fghij	80	--
3	lmnop	70	--
4	qrstu	60	--
5	vwxyz	50	350

Omprakash Kukana · Answer 5 · 2013-09-17T21:29:00

Solution 2

SELECT SUM(Marks) FROM table_name;
u can find out sum of a column like this method

Posted 17-Sep-13 21:29pm

Omprakash Kukana

Comments

nsvrao 18-Sep-13 4:28am

Thanks for your solution...
But I want that sum in another column in the last row
the remaining columns should be "-".