且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

sql中的列总和

更新时间:2023-02-10 21:59:55

这样的事情:

  SELECT  
sub1,
sub2,
sub3,
(sub1 + sub2 + sub3) as ' 总标记数
FROM 学生


请尝试以下代码段。它使用ROLLUP功能。



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

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





这会给你输出像



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


您好nsvrao请使用此sql脚本我测试了它结果如你所愿。



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







i正在努力简化这个选择查询,如果可能的话



问候


I have a table like this.

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

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...

Something like this:
SELECT 
sub1,
sub2,
sub3,
(sub1 + sub2 + sub3) as 'Total Marks'
FROM Student


Try below snippet. It uses ROLLUP feature.

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


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

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