David Blocker and Bill Downall,
You both should be PROUD of your work and the service to R:BASE Community! Very Best Regards, Razzak. At 07:06 AM 6/4/2002 -0500, David Blocker wrote: >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/
