On Tue, 04 Jun 2002 09:10:35 -0400, mjs wrote: >Can somebody give me an example of the syntax to create a single table >view using group by and having? If I have a table with 3 coulums, rdate >service and price, and I want the view to show just 1 row for each >service and some permutation of the price (such as min, max, last, >first, count, etc), what magic sentence would make that view? >
>From David M. Blocker's "Introduction to R:Base for Windows:" ======================================= The SQL Aggregate Functions are MIN(), MAX(), AVG(), SUM(), and COUNT() Rules to write a SELECT ... GROUP BY The grammar of this command is very strict 1) Name the column containing the values you want to group by TWICE: *) after the word SELECT *) after the words GROUP BY Name the column BY ITSELF, NOT within an aggregate function 2) Use at least one aggregate function (MIN, MAX, AVG, SUM, COUNT) etwen the words SELECT and FROM 3) SELECT NO other columns SELECT col1, SUM (col2) FROM table GROUP BY col1 GROUP BY is the ONLY time you can SELECT a mix of columns and aggregate functions. In all other SELECTs you either name NO aggregate functions, or EVERYTHING is an aggregate function. .... GROUP BY: Sorting, Excluding Groups SELECT col1, SUM (col2) FROM table GROUP BY col1 + HAVING SUM (col2) > 1000 + ORDER BY 2 A WHERE clause excludes ROWS, BEFORE calculation of aggregate functions A HAVING clause excludes GROUPS of ROWS, AFTER the calculation ---- A WHERE clauses comes BEFORE the GROUP BY clause the HAVING clause comes immediately after the GROUP BY clause ---- HAVING works ONLY with SELECT ... GROUP BY ORDER BY cannot explicitly use and expression or aggregate function, but you can sort by these using the "sequence number." To determine what "Sequence number" to use, count the position of the aggregate function in the list of items after the word SELECT and before the word FROM Bill ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
