sql中的列总和 [英] sum of columns in sql
本文介绍了sql中的列总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张这样的桌子。
Sno | SNAME |标记
001 | ABCDE | 90
002 | FGHIJ | 80
003 | lmnop | 70
004 | qrstu | 60
005 | VWXYZ | 50
我想要像这样总计
Sno | SNAME |标记|总计
001 | ABCDE | 90 | -
002 | FGHIJ | 80 | -
003 | lmnop | 70 | -
004 | qrstu | 60 | -
005 | VWXYZ | 50 | 350
请帮忙...
提前致谢...
解决方案
这样的事情:
SELECT
sub1,
sub2,
sub3,
(sub1 + sub2 + sub3) as < span class =code-string>' 总标记数
FROM 学生
请尝试以下代码段。它使用ROLLUP功能。
DECLARE @ Table 表(Sno VARCHAR ( 3 ),Sname VARCHAR ( 20 ),标记 INT )
INSERT INTO @ Table VALUES (' 001',' abcde', 90 )
,(' 002',' fghij', 80 )
,(< span class =code-string>' 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
' - '
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
这篇关于sql中的列总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文