CODEWITHSUNDEEP
sqlsql-servergroup-byaggregate-functions
JohnN
JohnN

Reputation: 1010

How does group by work in sub queries?

I'm pretty shaky when it comes to advanced aggregate functions in SQL. I've got this query:

SELECT Name, avg(Value) Mean, Number
From Table1
where Category = 'Time'
group by Name, Number;

which is a fairly obvious in what it does. But in case, here's what that looks like:

|  Name  |  Mean | Number |
|:------:|:-----:|:------:|
| Thing1 | 112.6 | 1      |
| Thing2 | 111.9 | 1      |
| Thing1 | 109.1 | 2      |
| Thing2 | 99.4  | 2      |
| Thing1 | 91.8  | 3      |
| Thing2 | 80.1  | 3      |
| Thing1 | 73.7  | 4      |
| Thing2 | 47.2  | 4      |

Now I'd like to get the sum of these averages for a particular Number.

My thought process would be to do something like this:

SELECT SUM(Mean) Total Mean, Number 
FROM (SELECT Name, avg(Value) Mean, Number
    FROM Table1
    WHERE Category = 'Time'
    GROUP BY Name, Number)
GROUP BY Number;

But as is probably obvious, I get a Incorrect syntax near the keyword 'group' error. What is the best way to accomplish this goal so that I get a table that looks like this:

| Total Mean | Number |
|:----------:|:------:|
| 224.5      | 1      |
| 208.5      | 2      |
| 171.9      | 3      |
| 120.9      | 4      |

Upvotes: 0

Views: 31

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Your derived table is missing an alias.

SELECT SUM(Mean) Total Mean, Number 
FROM (SELECT Name, avg(Value) Mean, Number
    FROM Table1
    WHERE Category = 'Time'
    GROUP BY Name, Number) t --alias for the derived table
GROUP BY Number;

Upvotes: 3

Related Questions