使用查询在SQL中选择同一组的离散总和. [英] select distict sum of same group in SQL using query.
问题描述
朋友,我有一个表,该表具有colum grp和标记,我想从该表中获得标记的总和,但条件是,如果grp相同,则它计数单行,请看表..
表tbgrp
grp标记
1 5
2 4
2 4
3 5
3 5
3 5
我使用查询从tbgrp中选择总和(标记).但显示结果28,实际计数为14,因为5在1组中,4在2组中,5在3组中,所以5 + 4 + 5 = 14但结果为28.
请帮助我,我非常困惑如何获取它.
感谢advace
Parveen Rathi
hi friend i have a table having colume grp and marks i want to get sum of marks from table but the condition is that if the grp is same then it count single rows let see the table..
Table tbgrp
grp marks
1 5
2 4
2 4
3 5
3 5
3 5
i using the query select sum(marks) from tbgrp. but it shows the result 28, in real count is 14 because 5 is in 1 group and 4 is in 2 group and 5 is in 3 group so 5+4+5=14 but the result is 28.
plz help me i am very confuse how to get it.
Thanks in advace
Parveen Rathi
推荐答案
Parveen Rathi
使用此查询
Hi Parveen Rathi
Use this query
SELECT Sum(marks) FROM (SELECT grp,marks,ROW_NUMBER() OVER
(PARTITION BY grp ORDER BY grp) AS RowNumber
FROM Table2 ) as RankedTable WHERE RowNumber=1
*使用表名代替Table2
如果组中的标记始终相同,则只需使用Avg
聚合或使用上述查询.
* Use your table name in the place of Table2
If the marks in the group always same then simply use an Avg
aggregate or else use the above query.
with distinctTable as
(
select distinct * from tbgrp
)
select sum(marks) from distinctTable
这篇关于使用查询在SQL中选择同一组的离散总和.的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!