Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Reporting with SQL Aggregate and Numeric Functions Calculating Averages

Mike Booth
Mike Booth
2,035 Points

Calculating Averages

What if I wanted to use the group by, but also wanted to order it in descending order with the highest cost avg first? I tried using group by and order by but it threw an error

2 Answers

I think this might be the query you are looking for:

SELECT AVG(cost) as average, user_id FROM orders GROUP BY user_id ORDER BY average DESC;

Ari Misha
Ari Misha
19,323 Points

Hiya Mike! There is a difference between "GROUP BY" and "ORDER BY", which i think is a really important concept.

"ORDER BY" alters the order in which items are returned.

"GROUP BY" will aggregate records by the specified columns which allows you to perform aggregation functions on non-grouped columns (such as SUM, COUNT, AVG, etc).

Here is the example to make it more understanding and concise:

TABLE:
ID NAME
1  Peter
2  John
3  Greg
4  Peter

SELECT *
FROM TABLE
ORDER BY NAME

= 
3 Greg
2 John
1 Peter
4 Peter

SELECT Count(ID), NAME
FROM TABLE
GROUP BY NAME

= 
1 Greg
1 John 
2 Peter

SELECT NAME
FROM TABLE
GROUP BY NAME
HAVING Count(ID) > 1

=
Peter