Hey Bill thanks for the quote, but you shouldn't be so modest - you co-authored it, remember?
David ----- Original Message ----- From: "Bill Downall" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, June 04, 2002 9:13 AM Subject: Re: views/group by/having > 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/ > ================================================ 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/
