I'm trying to write a SQL query that will return a list of aggregated values; however, I want to group the query by one of the aggregated values (a count):
我正在尝试编写一个SQL查询,它将返回聚合值列表;但是,我想通过其中一个聚合值(计数)对查询进行分组:
select t.Field1, count(distinct(t.Field2), SUM(t.Value1)
from MyTable t
group by t.Field1, count(t.Field2)
I've tried putting the count into a subquery, and putting the whole query into a subquery and grouping there. Is there an way to do this that doesn't involve creating a temporary table (I don't have anything against temporary tables per se).
我已经尝试将计数放入子查询中,并将整个查询放入子查询并在那里进行分组。有没有办法做到这一点,不涉及创建临时表(我没有任何针对临时表本身的东西)。
The desired outcome would look like this:
期望的结果如下:
Field1 Count Sum
----------------------------------------------------
CAT1 3 19.5
CAT1 2 100
CAT2 2 62
The data that I'm working with looks like this:
我正在使用的数据如下所示:
Field1 Field2 Field3 Value1
-----------------------------------------------------
CAT1 1 1 5
CAT1 2 1 2.5
CAT1 3 1 12
CAT1 4 2 50
CAT1 5 2 50
CAT2 6 3 50
CAT2 7 3 12
So, I want a grouping by the number of distinct Field2 values per Field3
所以,我希望按每个Field3的不同Field2值的数量进行分组
1 个解决方案
#1
1
If I understand you correctly, then the follow should work.
如果我理解正确,那么以下内容应该有效。
select Field1 , Count , Sum(Value1)
from
(
select t.Field1, count(*) as Count, SUM(t.Value1) as Value1
from MyTable t
group by t.Field1, t.Field3
)
as t2
group by Field1, Count
#1
1
If I understand you correctly, then the follow should work.
如果我理解正确,那么以下内容应该有效。
select Field1 , Count , Sum(Value1)
from
(
select t.Field1, count(*) as Count, SUM(t.Value1) as Value1
from MyTable t
group by t.Field1, t.Field3
)
as t2
group by Field1, Count