More useful GROUP BY stuff.

By admin on Oct 18, 2008 | In Daily Commute - Standard hints/tips | Send feedback »

Hi, I've not gone into hibernation (yet)! I've been busy working on SqlServer queries in my job. One useful clause I use there is "top" to limit how many rows your query brings back. Now of course we have an equivalent in Mysql, the "LIMIT" clause. This is very useful to impose order on the rows your query brings back; you don't just have the facility to restrict the query to the first x rows. You can use OFFSET to skip rows. For example if I want to bring back 5 rows from the table EMP, but not the 5 lowest values, I could use the following statement:

Code:

select sal from emp order by sal limit 5 offset 5;
+------+
| sal  |
+------+
| 1250 |
| 1300 |
| 1500 |
| 1600 |
| 2450 |
+------+
5 rows in set (0.01 sec)

Now another useful point about the LIMIT clause is you can use it on GROUP BY queries. It makes sense really, as the LIMIT clause is working on the record rows that are returned for display. Imagine that you need to group by a certain field and sum on a key value. Using the GROUP BY and ORDER BY clauses together will bring back results in ascending or (if specified by DESC) order. However, it may be that large data sets will produce more rows than you're interested in. Using the LIMIT clause will restrict the number of rows you bring back:

Code:

mysql> select deptno, sum(sal) from emp
    -> group by deptno
    -> order by sum(sal) desc
    -> limit 2;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|     20 |    10875 |
|     30 |    10650 |
+--------+----------+

I think the combination of GROUP BY with other functions and clauses, is what really makes sql powerful in analyzing data-sets. The following code demonstrates this, as it relies on the use of the UNION operator (remember to use the same number of columns and column data types in each select statement) to display different summaries in the same report:

Code:

mysql> select deptno, job,
    -> 'TOTAL BY DEPT AND JOB' as category,
    -> sum(sal) as sal
    -> from emp
    -> group by deptno, job
    -> UNION ALL
    -> select deptno, null,
    -> 'TOTAL BY DEPT', sum(sal)
    -> from emp
    -> group by deptno
    -> UNION ALL
    -> select null, null,
    -> 'GRAND TOTAL FOR TABLE', sum(sal)
    -> from emp;
+--------+-----------+-----------------------+-------+
| deptno | job       | category              | sal   |
+--------+-----------+-----------------------+-------+
|     10 | CLERK     | TOTAL BY DEPT AND JOB |  1300 |
|     10 | MANAGER   | TOTAL BY DEPT AND JOB |  2450 |
|     10 | PRESIDENT | TOTAL BY DEPT AND JOB |  5000 |
|     20 | ANALYST   | TOTAL BY DEPT AND JOB |  6000 |
|     20 | CLERK     | TOTAL BY DEPT AND JOB |  1900 |
|     20 | MANAGER   | TOTAL BY DEPT AND JOB |  2975 |
|     30 | CLERK     | TOTAL BY DEPT AND JOB |   950 |
|     30 | MANAGER   | TOTAL BY DEPT AND JOB |  2850 |
|     30 | SALESMAN  | TOTAL BY DEPT AND JOB |  6850 |
|     10 | NULL      | TOTAL BY DEPT         |  8750 |
|     20 | NULL      | TOTAL BY DEPT         | 10875 |
|     30 | NULL      | TOTAL BY DEPT         | 10650 |
|   NULL | NULL      | GRAND TOTAL FOR TABLE | 30275 |
+--------+-----------+-----------------------+-------+
13 rows in set (0.54 sec)

Pretty useful, hey? Before I leave, I have to admit to getting most of my code examples this time from the book SQL CookBook, published by O'Reilly. This really is a book worth purchasing, even if you're just starting in SQL or Mysql programming. You can find the book at this site booklink
Plug over - I wish you a profitable time working with your database of choice, and I hope you visit my site again soon.

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)
« Checking for non-text entries.Beautiful code »