zimmer9
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.