GROUP BY Clause Help

[$Three3]

Hey everyone, I am new to MySQL and I have been having a hard time understanding the GROUP BY clause. I have not been able to find a basic example that will let me grasp what it actually does. I am currently reading a MySQL book and here is the code from the book that uses the GROUP BY clause:

 

SELECT state, COUNT(*) AS count FROM president GROUP BY state ORDER BY count DESC;

 

And here is the output from that query:

 

+-------+-------+ 
| state | count |
+-------+-------+
| VA | 8 |
| OH | 7 | 
| MA | 4 | 
| NY | 4 | 
| NC | 2 | 
| VT | 2 | 
| TX | 2 | 
| SC | 1 | 
| NH | 1 | 
| PA | 1 | 
| KY | 1 | 
| NJ | 1 | 
| IA | 1 | 
| MO | 1 |
| CA | 1 | 
| NE | 1 | 
| GA | 1 | 
| IL | 1 | 
| AR | 1 | 
| CT | 1 | 
+-------+-------+

 

Any help explaining what the GROUP BY clause actually does and how it works would be great. I understand the rest of the code except for the GROUP BY part. Thanks in advance for the help.

 

 

[agrant]

The best way to explain group by is it allows you to place records into groups based on values that are stored in the designed columns.

 

for example:

select gender, count(*) as ct from people group by gender

 

would allow you to group on the column gender, so you would expect to see something like

 

gender --- ct

M --- 50

F --- 40

 

50 records would have the gender value of M, 40 would have the value of F

 

All of your columns that you have in your select need to be in your group by also... and then in your select you can use math functions such as MAX, MIN, SUM, AVG, COUNT

 

mySQL has a good article on the usage of the group by function that you should take a look at if you haven't yet: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

 

I hope all of that makes sense,

Aaron