• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

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
0
zimmer9
Asked:
zimmer9
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now