CODEWITHSUNDEEP
mysqlsql
shekwo
shekwo

Reputation: 1447

Getting the average in a GROUP BY statement

I am trying to get the average of numbers in a query that uses both GROUP BY and ORDER BY but I don't know how to go about this.

This is my query:

SELECT COUNT(id) as tcount 
FROM issues 
GROUP BY location_id 
ORDER BY tcount DESC

How do I get the average here?

Upvotes: 0

Views: 49

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

Just use AVG aggregate function :

SELECT location_id, AVG(id) as tavg 
  FROM issues 
 GROUP BY location_id 
 ORDER BY 1 DESC;

Shall be more meaningful to remove GROUP BY and ORDER BY:

SELECT AVG(id) as tavg 
  FROM issues;

SQL Fiddle Demo

Upvotes: 0

Rafael Araújo
Rafael Araújo

Reputation: 201

Use AVG()

SELECT AVG(fieldtoaggregate) as average_value FROM table

If you want the average grouped by a field, you can use group by. Imagine you want the avegare age grouped by gender:

SELECT AVG(age) as average_age, gender FROM tb_user GROUP BY gender ORDER BY gender ASC;

Upvotes: 1

kjmerf
kjmerf

Reputation: 4345

I think you want:

SELECT AVG(tcount)
FROM
(
SELECT COUNT(id) as tcount 
FROM issues 
GROUP BY location_id
) sub

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You question is not clear but seems you are looking for AVG()

SELECT COUNT(id) as tcount , avg(id) 
FROM issues 
GROUP BY location_id 
ORDER BY tcount DESC

or in general 

SELECT COUNT(id) as tcount , avg(your_column) 
FROM issues 
GROUP BY location_id 
ORDER BY tcount DESC

Upvotes: 0

Related Questions