Group by ?

Group by ?

Post by Jan Helge Priva » Mon, 28 Aug 2000 04:00:00



I would like  to group the output like the SQL "Group by"-command, so the
category comes as a heading for simular ordered lines. Is that possible with
CDML ?

Thanks in advance

Jan Helge

 
 
 

1. Rolling Up a Group (or, Grouping a Group)

Hi,

I'm trying to figure out how to Group a Group. Here's a sample of the raw data:

Member  CoverageCode  InsuredDate  InsuredAmount
------  ------------  -----------  -------------
BOB     A             01/01/1999   1000
ALICE   A             01/01/2000   1000
BOB     A             01/01/2001   1000
BOB     B             07/01/2001   1000

Now, let's say that in a Rate table, I have the following

CoverageCode  EffectiveFrom  EffectiveTo  Rate
------------  -------------  -----------  ----
A             01/01/1999     12/31/1999   0.10
A             01/01/2000     12/31/2000   0.15
A             01/01/2001     12/31/2001   0.20
B             01/01/2001     12/31/2001   0.22

My stored procedure calculates, sums, and groups by coverage code:

Member  TotalInsuredAmount
------  ------------------
BOB     300
BOB     220
ALICE   150

What I need to get as a result set from my stored procedure is the following:

Member  TotalInsuredAmount
------  ------------------
BOB     520
ALICE   150

I think it creates two "BOB" records because each one is grouped by a Coverage Code (Bob has 2 coverage "A" and 1 coverage "B")

What would be the SQL I could use to Group the Group, or roll-up the subtotals into just one grand total for each Member?

Thanks,

Scott

2. MAKE MONEY FAST LEGALLY

3. Local Group or Global Group

4. US-NY-ROCHESTER-ORACLE

5. grouping records , and updating the first record in each group

6. Accessing tables from another Schema

7. n Of Each Group using GROUP BY

8. MS Access connect to SQL Server 11

9. Help -- Grouping qry ---- Sub Grouping -Is this possible?

10. Returning top n results for each group with a Group By Statement

11. Copy permissions from user/group to different user/group

12. Count of groups via group by

13. Remapping/Copying SQL Logins from NT Local Groups to Domain Global Groups