GROUP BY SQL

The GROUP BY phrase is used to "group" information within a column specifically to perform an aggregate function upon it. This is handy when we want to say add the total expenditures from a group of invoices from the same customer. The syntax in a SELECT statement would look like this:

SELECT column1, column2,
SUM(column_on_which_perform_function)
FROM table
GROUP BY column1, column2

Note that all columns being SELECTed must appear in the GROUP BY phrase. Do not forget the comma between the SELECT column and the SUM function. The statement will not work without it. To illustrate how GROUP BY works we will use our Monopoly property table:

prop_idnamepricegroup
1Baltic Ave60purple
2Mediterranean Ave60purple
3Reading Railroad200rr
4Oriental Ave100light blue
5Vermont Ave100light blue
6Connecticut Ave120light blue

We would like to know the value of all of the property in each color group. So we would use the following:

SELECT group,
SUM(price)
FROM monopoly_prop
GROUP BY group

The result of this SELECT statement using the GROUP BY is the following table:

320
groupSUM(price)
light blue
purple120
rr
200

Note that without the GROUP BY clause the result would have been a list of six rows with light blue listed three times, purple twice, and one rr, each with the price of one of the corresponding properties. The SUM function would have been useless.

< FOREIGN KEY Constraint SQL | HAVING SQL >


Send notes in disappearing ink!

Interesting Pages