如何从sql查询中获得结果总和 [英] how to get sum of result from sql query

问题描述

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.QADetailsnew 
insert 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 values
 
	SELECT LEFT(Username, CHARINDEX(',', Username )-1) AS SingleUser, Quality, RIGHT(Username, LEN(Username) - CHARINDEX(',', Username)) AS Remainder
	FROM @dd
	WHERE CHARINDEX(',', Username)>0
	union all
	select Username as SingleUser,Quality, NULL as Remainder
	FROM @dd
	WHERE CHARINDEX(',',Username )=0
	
        -- here starts recursive part
	UNION ALL
	SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleUser, Quality, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM UserTotals 
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Remainder AS SingleUser, Quality, NULL AS Remainder
	FROM UserTotals
	WHERE CHARINDEX(',', Remainder)=0
)
select distinct(SingleUser),(sum(Quality)/COUNT(SingleUser)) as QualityPercentage, 100-sum(Quality)/COUNT(SingleUser ) as ErrorPercentage
from UserTotals 

group by SingleUser  

SINGLEUSER    Quality%         Error%

John	        100		0
Robinson	100		0
Mary	        99		1
Rada		66		34
Anurag		55		45
Ashish		32		68
Denzil		98		2
Reena	        88		12
Martin		55		45
Robinson	94		6

我有两张来自不同数据库的表。第一个数据库是 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.

推荐答案

替换为：

Replace this:

select distinct(SingleUser),(sum(Quality)/COUNT(SingleUser)) as QualityPercentage, 100-sum(Quality)/COUNT(SingleUser ) as ErrorPercentage
from UserTotals

with：

with:

SELECT SingleUser, AVG(Quality) as QualityPercentage, 100-AVG(Quality) as ErrorPercentage
FROM UserTotals
GROUP BY SingleUser

如需了解更多信息，请参阅：

AVG（T-SQL） [ ^ ]

GROUP BY（T-SQL） [ ^ ]

汇总功能 [< a href =https://msdn.microsoft.com/en-us/library/ms173454.aspxtarget =_ blanktitle =新窗口> ^ ]

For further information , please see:
AVG (T-SQL)[^]
GROUP BY (T-SQL)[^]
Aggregate functions[^]

这篇关于如何从sql查询中获得结果总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！