NetNews Usenet Archive 1992 #30/NN_1992_30.iso/spool/comp/database/ingres/2080

Text File | 1992-12-13 | 1.9 KB | 50 lines

Newsgroups: comp.databases.ingres
Path: sparky!uunet!zaphod.mps.ohio-state.edu!darwin.sura.net!uvaarpa!cv3.cv.nrao.edu!mail-to-news-gateway
From: "Anthony J. Rzepela" <[e-mail]>
Subject: Re: Need help with GROUP BY clause
Message-ID: <[e-mail]>
Sender: [e-mail] (Operator)
Organization: National Radio Astronomy Observatory
Date: Sat, 12 Dec 1992 18:41:05 GMT
Lines: 39

 In article <[e-mail]> [e-mail] (Robert Perlberg) writes
 about needing to group his securites by year, and then performing
 a summary function (totalling some dollar amounts) on another field.

 He was dismayed because GROUP BY was insufficient, and was worried that
 he would havee to go to some non-SQL forum.  Well, with *SOME* ongoing
 work required (updateing the SQL statement every year) one can avoid
 dummy fields, auxiliary tables, and the like.

 One can use summary functions in the select-list clause of subqueries in
 a UNION query.

 For example, the following query:


 select year = 1990, total_number_studies = count(study_date)
 from   studies
 where  study_date > '31-dec-1989' and study_date < '1-jan-1991'

 union

 select year = 1989, total_number_studies = count(study_date)
 from   studies
 where  study_date > '31-dec-1988' and study_date < '1-jan-1990'

 produces a two-line result, with the year, and summary function, in each row.
 UNION queries have their own behaviour peculiarities, and there are other
 shortcomings to this method which should be fairly obvious, but it IS a
 short term-solution to doing it in pure SQL.

 NB: for an ongoing thing, one could use the REPORT WRITER to automatically
 generate an SQL statement like the one above which would produce no
 "zero" rows, and would not require the ongoing maintenance.   In *ANY*
 case, long story short is that it *CAN* be done in SQL.


 ---------------------------------------
 Tony