如何从sql查询中获得结果总和

本文介绍了如何从sql查询中获得结果总和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述DECLARE @dd TABLE (Username VARCHAR(250), WorkArea VARCHAR(50), Scope VARCHAR(50), Quality INT)insert into @dd (Username ,WorkArea ,Scope ,Quality) select QAResource,WorkArea,ProjectScope ,quality from[PMT] .dbo.QADetailsnewinsert into @dd (Username ,WorkArea ,Scope ,Quality)select a.resources,a.work_area,a.scope, a.Quality from [workalloc] .[dbo] .work a;WITH UserTotals AS( -- initial valuesSELECT LEFT(Username, CHARINDEX(',', Username )-1) AS SingleUser, Quality, RIGHT(Username, LEN(Username) - CHARINDEX(',', Username)) AS RemainderFROM @ddWHERE CHARINDEX(',', Username)>0union allselect Username as SingleUser,Quality, NULL as RemainderFROM @ddWHERE CHARINDEX(',',Username )=0 -- here starts recursive partUNION ALLSELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleUser, Quality, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS RemainderFROM UserTotalsWHERE CHARINDEX(',', Remainder)>0UNION ALLSELECT Remainder AS SingleUser, Quality, NULL AS RemainderFROM UserTotalsWHERE CHARINDEX(',', Remainder)=0)select distinct(SingleUser),(sum(Quality)/COUNT(SingleUser)) as QualityPercentage, 100-sum(Quality)/COUNT(SingleUser ) as ErrorPercentagefrom UserTotalsgroup by SingleUserSINGLEUSER Quality% Error%John 1000Robinson1000Mary 991Rada6634Anurag5545Ashish3268Denzil982Reena 8812Martin5545Robinson946 我有两张来自不同数据库的表。第一个数据库是 PMT ，其中我已经命名为 QADetailsnew 表。 在第二个数据库中是 Workalloc ，表名是 work 。 在两个表中，我都有用户名Robinson的条目，它被执行两次，因为在不同数据库的两个表中都有条目我需要得到两者的总和。我使用了关键字 distinct 但在这种情况下它不起作用。I have two tables from different databases. First database is PMT in which i have named QADetailsnew Table.In the second database is Workalloc and the table name is work.In both of the tables i have the entry for the Username Robinson which gets executed twice because there is entry in the both the tables in different databases i need to get the sum of both. I have used the keyword distinct but it doesnt work in this case.推荐答案select distinct(SingleUser),(sum(Quality)/COUNT(SingleUser)) as QualityPercentage, 100-sum(Quality)/COUNT(SingleUser ) as ErrorPercentagefrom UserTotals with：with:SELECT SingleUser, AVG(Quality) as QualityPercentage, 100-AVG(Quality) as ErrorPercentageFROM UserTotalsGROUP BY SingleUser 如需了解更多信息，请参阅： AVG（T-SQL） [ ^ ] GROUP BY（T-SQL） [ ^ ] 汇总功能 [ ^ ]For further information , please see:AVG (T-SQL)[^]GROUP BY (T-SQL)[^]Aggregate functions[^] 这篇关于如何从sql查询中获得结果总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！