Dear Razzak Thanks for the kind words. We are proud, but not nearly as much as we are proud of what you've done for all of us!!!
David Blocker ----- Original Message ----- From: "A. Razzak Memon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, June 04, 2002 6:13 PM Subject: Re: views/group by/having > > 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/ > ================================================ 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/
