Paul Michaels
Paul Michaels

Reputation: 16685

Group by a count() clause in SQL Server

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

Answers (1)

sgmoore
sgmoore

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

Related Questions