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.
Friday, January 11, 2008
Subscribe to:
Post Comments (Atom)

No comments:
Post a Comment