更新时间: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 likeSno 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