sql sum查询问题 [英] sql sum query problem
本文介绍了sql sum查询问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
id name manecount femalecount
1 OC 1 1
2 BC-a 4 5
3 BC-b 2 3
4 BC-c 1 2
现在我想要的结果就像我只有一列BC一样,我需要BC-a的男性和女性总数, BC-b,BC-b
喜欢这个
id name manecount femalecount
1 OC 12 13
2 BC 7 10
解决方案
试试这个希望它有用或给出一些正确方向的提示。< br $>
SELECT CAT,SUM(MC),SUM(FC)< span class =code-keyword> FROM (
选择 ID, LEFT (NAME, 2 )CAT,mc,FC 来自表1)P
GROUP BY CAT
这可以帮助
CREATE TABLE BCounts
(ID INT IDENTITY ,Bcon VARCHAR ( 10 ),Val1 INT ,Val2 INT )
INSERT BCounts VALUES (' OC', 1 , 1 ),(' BC-a', 4 , 5 ),(' BC-b', 2 ,< span class =code-digit> 3 ),(' BC-c' , 1 , 2 )
SELECT LEFT (B.Bcon, 2 )Col1,SUM(B.Val1) Col2,SUM(B.Val2)Col2 FROM BCounts B
GROUP BY LEFT (B.Bcon, 2 )
Col1 Col2 Col3
BC 7 10
OC 1 1
id name manecount femalecount
1 OC 1 1
2 BC-a 4 5
3 BC-b 2 3
4 BC-c 1 2
now i want result like i have only one column BC in that i need total male and female count of BC-a, BC-b,BC-b
like this
id name manecount femalecount
1 OC 12 13
2 BC 7 10
解决方案
Try this hope its useful or give some hint in right direction.
SELECT CAT,SUM(MC),SUM(FC) FROM ( select ID,LEFT(NAME,2) CAT,mc,FC from Table1) P GROUP BY CAT
May this helps
CREATE TABLE BCounts (ID INT IDENTITY,Bcon VARCHAR(10), Val1 INT, Val2 INT) INSERT BCounts VALUES('OC',1,1),('BC-a',4,5),('BC-b',2,3),('BC-c',1,2) SELECT LEFT(B.Bcon,2) Col1, SUM(B.Val1) Col2 ,SUM(B.Val2) Col2 FROM BCounts B GROUP BY LEFT(B.Bcon,2)
Col1 Col2 Col3 BC 7 10 OC 1 1
这篇关于sql sum查询问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文