Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

Is it possible to create subtotal and grandtotal records via SQL Statements along with detail records using SQL Server 2005 with tables?

I have a SQL SELECT statement as follows:

SELECT ' ', MthTitl, DFrC, DToC, DollTitl, CountOfCustomerNumber, SumOfAcctValue
INTO tblAgingReport
FROM tblRange order by MthF, DollTitl Desc

that produces the following results:

Tot   DtRng                            DtFrom     DtTo           DolRng              Freq         DollAmt
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH 100k    778   647551536.09
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH   50k    604          36345.55
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH   25k    463            4754.47
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH   10k    745       4686476.85
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH     1k    964     86447685.37
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH     0k    142            7453.56
        6 MOS TO 1 YR           12/09/01  12/02/01 WITH 100k    778           51536.09
        6 MOS TO 1 YR           12/09/01  12/02/01 WITH   50k    604              345.55
        6 MOS TO 1 YR           12/09/01  12/02/01 WITH   25k    463          56754.47
        6 MOS TO 1 YR           12/09/01  12/02/01 WITH   10k    745            4246.85
        6 MOS TO 1 YR           12/09/01  12/02/01 WITH     1k    964          11685.37
        6 MOS TO 1 YR           12/09/01  12/02/01 WITH     0k    142          45763.56

Is there a way to write a SQL Statement to create subtotal and grand records in addition to the detail records? For example, to create the following sub and grand total records in addition to the detail records?

        LESS THAN 6 MOS   12/03/01  12/08/01 WITH 100k    778   647551536.09
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH   50k    604          36345.55
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH   25k    463            4754.47
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH   10k    745       4686476.85
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH     1k    964     86447685.37
        LESS THAN 6 MOS   12/03/01  12/08/01 WITH     0k    142            7453.56
Sub  LESS THAN 6 MOS                                                                     3,696  738734251.89  
           6 MOS TO 1 YR        12/09/01  12/02/01 WITH 100k    448           51536.09
           6 MOS TO 1 YR        12/09/01  12/02/01 WITH   50k    114              345.55
           6 MOS TO 1 YR        12/09/01  12/02/01 WITH   25k    775          56754.47
           6 MOS TO 1 YR        12/09/01  12/02/01 WITH   10k    375            4246.85
           6 MOS TO 1 YR        12/09/01  12/02/01 WITH     1k    325          11685.37
           6 MOS TO 1 YR        12/09/01  12/02/01 WITH     0k    875          45763.56
Sub     6 MOS TO 1 YR                                                                         2,912        170331.89  
GT                                                                                                               6,608   738904583.78
SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial