Sql Group By

Adding Sql group by to the Select statement will return groups of rows. This command is closely related to sql having which puts constrain to the results of group by.

The syntax:

SELECT column_list,
       Aggretate_function()
FROM   table_name [WHERE conditions ]
GROUP  BY column_list 

Notice that the Group By puts together rows into sets (depending on the column_list) and the aggregate function returns a summary value (count, min, max or average).

Make sure all fields in the select list also appear in the Group By list.

First example on how to use the Group By, here is the Contact Table

CPIdSurnameFirstnameAddressSuburbStatePostcode
1 White Alan 210 George St Sydney NSW 2000
2 Johnson Benjamin 11 Cooper St Surry Hill NSW 2010
3 Brown Peter 72 Haymarket St Haymarket NSW 2000
4 Anderson Thomas 33 Collins St Melbourne VIC 3000
5 Gomdas Tony 78 Marine Pde Coolangatta QLD 4225
6 Jones David 2 Park St Sydney NSW 2000
7 Gutman Philip 27 Swanson St Melbourne VIC 3000

SQL Statement:

SELECT state,
       COUNT(*) AS total
FROM   contacts
GROUP  BY state 

Result:

StateTotal
NSW 4
VIC 2
QLD 1

You can add an extra layer to the grouping by selecting one more column in the SELECT statement. Another word, you are creating a subset of the first column.

SQL Statement:

SELECT state,
       suburb,
       COUNT(*) AS total
FROM   contacts
GROUP  BY state,
          suburb 

Result:

StateSuburbTotal
NSW Sydney 2
NSW Surry Hill 1
NSW Haymarket 1
VIC Melbourne 2
QLD Coolangatta 1

Common mistake:
The following SQL gives an error, see if you can spot the problem!

SQL Statement:

SELECT state,
       suburb,
       COUNT(*) AS total
FROM   contacts
GROUP  BY state 

Have you spotted the issue?

The above Sql shows a common mistake when you use Sql Group By. The issue here is that we didn't put all columns that we use in the Select statement. To fix it, just match the column_list in Select statement with the column_list in the Group By. The correct Sql is the same as the second example above.

Quick link. From here, you can learn the following:

Sql Select
The core of Sql, selecting data from table.

Sql Where
Conditionally selecting rows from table.

Sql Distinct
Eliminating duplicate values in the output.

Sql Count
Counting number of rows.

Sql Sum
Getting the sum of a field

Sql Max
This function will return the highest value.

Sql Min
This function will return the lowest value.

Sql Avg
This function will return the average value.

Sql Order By
Sorting the output

Sql And Or
Connecting two or more conditions in the Where or Having clause.

Sql Having
To filter the result of aggregation

Sql Like
Pattern searching for conditions in Where or Having clause

Sql Between
Added to conditions to show results between two values.

Journey
Buy at AllPosters.com
A bend in the road is not the end of the road... unless you fail to make the turn

Positive Resources

All of us have at least two or three things that excite us. We could talk about these subjects for hours. Good news!. Did you know that Site Built It! can turn this type of deep interest and enthusiasm into a revenue-generating business?

Review some of the Site Build It! case studies and you'll absolutely discover something that ignites more positive ideas into your mind.

SBI! Video Tour


Invest the most
important 30 minutes
you will spend online.

Click, sit back, and
understand why SBI!
makes you succeed.