Friday, January 11, 2008

SQL GROUP BY Best Practice

SQL's GROUP BY clause can be an increadibly powerful tool for generating report data. It can, however, be a little tricky to get just right.

What it does is to "summarise" a set of data using a simple aggregate function (COUNT, MAX, MIN, SUM, etc...). In the multidimensional DB world (Oracle Express) we refer to these as "rollup axis" or "dimensions".

For example:

SELECT id, MAX(time), COUNT(id)
FROM table_1
GROUP BY id

Will display the greatest time for each id in the table and will give you a count of the number of times an entry with a unique ID exists in that table.

One of my co-workers pointed out a best practice that I'd totally forgotten about:

ALWAYS PUT THE AGGREGATES TO THE RIGHT OF THE LABELS AND MAKE SURE EXACTLY THE SAME LABELS APPEAR IN THE GROUP BY.

No comments: