有可能用by来计算一列的总数吗?

I have a sql query which produces the following table, But I want to be able to obtain the total for the marked columns which are grouped by category:

我有一个sql查询，它生成下面的表，但是我希望能够获得按类别分组的标记列的总数:

here is the code:

这是代码:

select pic.Name as Category,
pis.Code as Code,
pis.Name as Issue,
count(it.ID) as  'total count',
sum(mc.ots) as 'Imps',
sum(case when ito.rating <50 then 1 else 0 end) as 'unfav count',
sum(case when ito.Rating =50  then 1 else 0 end) as 'neu count',
sum(case when ito.Rating >50  then 1 else 0 end) as 'fav count',

(sum(case when ito.rating < 50 then 1.0 else 0.0 end) / count(it.ID) * 100) as 'unfav %',
(sum(case when ito.Rating =50  then 1.0 else 0.0 end) / count(it.ID) * 100) as 'neu %',
(sum(case when ito.Rating >50  then 1.0 else 0.0 end) / count(it.ID) * 100) as 'fav %',

CONVERT(decimal(4,2),avg(ito.Rating)) as 'Av Rating %',

count(it.id) * 100.0 / sum(count(it.ID)) OVER () AS [% of Count],
sum(mc.ots) * 100.0 / sum(sum(mc.ots)) OVER () AS [% Imps]

from 
Profiles P 
INNER JOIN ProfileResults PR ON P.ID = PR.ProfileID
INNER JOIN Items it ON PR.ItemID = It.ID
inner join Batches b on b.ID=it.BatchID
left outer join BatchActionHistory bah on b.ID=bah.batchid
inner join itemorganisations oit (nolock) on it.id=oit.itemid
inner join itemorganisations ito (nolock) on it.id=ito.itemid
inner join itemorganisationIssues ioi (nolock) on ito.id=ioi.itemorganisationid
inner join ProjectIssues pis (nolock)on ioi.IssueID = pis.ID
inner join ProjectIssueCategories pic (nolock)on pic.ID = pis.CategoryID
inner join Lookup_ItemStatus lis (nolock) on lis.ID = it.StatusID
inner join Lookup_BatchStatus lbs (nolock) on lbs.ID = b.StatusID
inner join Lookup_BatchTypes bt on bt.id = b.Typeid
inner join Lookup_MediaChannels mc on mc.id = it.MediaChannelID


where p.ID = @profileID 
and b.StatusID IN (6,7)
and bah.BatchActionID = 6
and it.StatusID = 2
and it.IsRelevant = 1


Group BY pic.Name,pis.Name,pis.Code
order by pic.name

2 个解决方案

#1

You can do this using the sum() windows function. You can actually nest it with regular aggregation functions. So:

可以使用sum() windows函数来实现这一点。实际上，您可以用常规的聚合函数来嵌套它。所以:

sum(count(it.ID)) over () as TotalCnt,
sum(sum(mc.ots)) over () as TotalImps

This assumes that you are using SQL Server 2005 or greater.

这假定您正在使用SQL Server 2005或更高版本。

That does it for the "whole column". For a subgroup, such as those defined by a category, the same idea holds (as pointed out by Alexander):

这对于“整列”是成立的。对于一个子组，例如由一个类别定义的子组，同样的想法是成立的(如Alexander指出的):

sum(count(it.ID)) over (partition by category) as CategoryCnt, sum(sum(mc.ots)) over (partition by category) as CategoryImps

sum(count(it.ID))除以(partition by category)作为CategoryCnt, sum(sum(sum(sum(sum(sum)))除以(partition by category)作为category

2 个解决方案

#1

更多相关文章

随机推荐