Reputation: 16685
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):
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
Upvotes: 0
Views: 266
Reputation: 16067
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
Upvotes: 1