A little more SQL:

In my prior post for the SQL curios, I introduced you to SQL and covered the basics of querying called out by basic clauses and commands.

To revisit a basic query, you need the SELECT clause. It selects what records are to be retrieved. The FROM clause refers to the table you're requesting from and the WHERE clause gives a condition. You also might remember that you can use math functions as well, like SUM or in this case the average or AVG. It looks like this:

SELECT avg(SAL)
FROM EMP
WHERE JOB = 'CLERK';

In the case of this table EMP:

The resulting query would return the average salary selected from the EMP table where the job title was 'clerk'. And in this case, a single column would be returned with a value of $1037.50. Not even exciting enough to show you.

We could do this for each job one by one but what if we had hundreds of jobs and wanted the average for each? We could use GROUP BY.

GROUP BY enables you to use aggregate functions on groups of data returned from a query. Aggregate functions are those that count, sum, average, etc.

The basic syntax for a GROUP BY is as follows:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

If we go back to the EMP table:

SELECT avg(SAL), COUNT(*), JOB
FROM EMP
GROUP BY JOB;

The aggregate functions average and count are grouped by job.

In the resulting table, you can see we get just what we asked for.

While you can use the WHERE clause before you use the GROUP BY clause, the HAVING clause would be used to create a condition after GROUP BY to give you a resulting condition:

SELECT avg(SAL), count(*), JOB
FROM EMP
GROUP BY JOB
HAVING avg(SAL)> 1500;

which returns a shorter list where the salary is more than 1500:

To tie everything back to everything we've learned over the past two posts, we can

• CREATE TABLE

• SELECT & WHERE

• JOIN

• aggregate (SUM, AVG, COUNT)

• GROUP BY

• HAVING

If you knew not even how to say SQL, I know you now at least have the confidence to do that.

Cheers!