Mike, Here are a couple of examples:
CREATE VIEW MagicView (Service,Rdate,NumRecords,MinPrice,MaxPrice,AvgPrice) AS + SELECT service, rdate,count(*),min(price),max(price),avg(price) FROM table + WHERE rdate BETWEEN this AND that + GROUP BY service,rdate + HAVING avg(price) > 500 This will give you one row for each service type and date with all the calcs for that record, but only if the average price on that date exceeds 500. CREATE VIEW MagicView2 (Service,MaxRdate,MinRdate,NumRecords,MinPrice,MaxPrice,AvgPrice) AS + SELECT service, max(rdate),min(rdate),count(*),min(price),max(price),avg(price) FROM table + WHERE rdate BETWEEN this AND that + GROUP BY service + HAVING avg(price) > 500 this will further summarize the first group, giving one record for each type of service, showing the date range of the values but only including those whose average price is > 500. Here are the key concepts: 1. The WHERE clause is applied to each record, BEFORE the grouping 2. The list of columns after the GROUP BY statement will first sort the results in that order, then process any expressions or calculations. Any column that is listed after the word SELECT that is NOT part of an expression or aggregate function MUST be listed after the GROUP BY or you will get an Illegal Column Specification error. 3. The HAVING clause calculates only AFTER the grouping takes place - it's like a WHERE clause, but on each GROUP. Good luck! ----------------------------------------------------------- Sami Aaron Software Management Specialists 19312 W 63rd Terr Shawnee KS 66218 913-915-1971 http://www.softwaremgmt.com ----- Original Message ----- From: "mjs" <[EMAIL PROTECTED]> To: "Rbase List Server" <[EMAIL PROTECTED]>; "rStreet yahoo" <[EMAIL PROTECTED]> Sent: Tuesday, June 04, 2002 8:10 AM Subject: views/group by/having > 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? > > TIA > > Mike Sinclair > > ================================================ > 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/
