Need help with GROUP BY clause 
Author Message
 Need help with GROUP BY clause


 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



Thu, 01 Jun 1995 02:41:05 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Need help with GROUP BY clause

2. Help in Group by clause

3. Help with a GROUP BY clause problem

4. PLEASE HELP: Problem with GROUP BY clause...

5. Delete a group of data, NEED NEED Help

6. EXISTS clause - help needed

7. Need help defining criteria for Where clause

8. Writing a SELECT with three possible WHERE CLAUSE (Need some help)

9. LIKE clause help needed

10. Need help refactoring FROM clause.

11. Need Help with AS Clause

12. Union Clause help needed


 
Powered by phpBB® Forum Software