asked on

How would you incorporate the ROLLUP extension of the GROUP BY to create SUBTOTAL records, in addition to the existing detail records, using SQL Server 2008?

How would you incorporate the ROLLUP extension of the GROUP BY to create SUBTOTAL records, in addition to the existing detail records, using SQL Server 2008?

I have a table named tblRange and create a query to geneate the following output:

SELECT MthTitl As [Date Range], DFrC as [From], DToC As [To], DollTitl As [Dollar Range],
CountOfCustomerNumber As [Num Of Accts], SumOfAcctValue As [Acct Value], MthF As [# Months],
DFr As [From Date ] FROM dbo.tblRange
ORDER By DFrC DESC, DollTitl DESC

result:

Date Range From To Dollar Range Num Of Accts Acct Value
-------------------------- ------------- -------------- ------------------- -------------------- ---------------
LESS THAN 6 MOS 2014-10-01 2015-03-01 WITH $100K 948 678648.46
LESS THAN 6 MOS 2014-10-01 2015-03-01 WITH $ 50K 662 456456.53
6 MOS TO 1 YEAR 2014-04-01 2014-09-01 WITH $ 100K 416 234245.54
6 MOS TO 1 YEAR 2014-04-01 2014-09-01 WITH $ 25K 473 145457.76
1 TO 2 YEARS 2013-04-01 2014-03-01 WITH $ 100K 598 854754.65
1 TO 2 YEARS 2013-04-01 2014-03-01 WITH $ 50K 1084 746953.64
1 TO 2 YEARS 2013-04-01 2014-03-01 WITH $ 10K 861 357735.22
2 TO 3 YEARS 2012-04-01 2013-03-01 WITH $100K 598 432864.35
2 TO 3 YEARS 2012-04-01 2013-03-01 WITH $ 50K 342 865676.54
3 PLUS YEARS 1931-12-01 2012-03-01 WITH $ 50K 113 253743.64
3 PLUS YEARS 1931-12-01 2012-03-01 WITH $ 10K 475 463435.56
3 PLUS YEARS 1931-12-01 2012-03-01 WITH $ 1K 735 356765.64
OTHER NULL NULL WITH $ 50K 375 575654.46
OTHER NULL NULL WITH $ 10K 153 453644.68

I would like to show the detail records along with subtotals for each of the distinct "Date Range" values for the 2 fields "Num Of Accts" and "Acct Value"

Date Range From To Dollar Range Num Of Accts Acct Value
-------------------------- ------------- -------------- ------------------- -------------------- ---------------
LESS THAN 6 MOS 2014-10-01 2015-03-01 WITH $100K 948 678648.46
LESS THAN 6 MOS 2014-10-01 2015-03-01 WITH $ 50K 662 456456.53
1610 1135104.99
6 MOS TO 1 YEAR 2014-04-01 2014-09-01 WITH $ 100K 416 234245.54
6 MOS TO 1 YEAR 2014-04-01 2014-09-01 WITH $ 25K 473 145457.76
889 379703.30
1 TO 2 YEARS 2013-04-01 2014-03-01 WITH $ 100K 598 854754.65
1 TO 2 YEARS 2013-04-01 2014-03-01 WITH $ 50K 1084 746953.64
1 TO 2 YEARS 2013-04-01 2014-03-01 WITH $ 10K 861 357735.22
2543 1959443.51
2 TO 3 YEARS 2012-04-01 2013-03-01 WITH $100K 598 432864.35
2 TO 3 YEARS 2012-04-01 2013-03-01 WITH $ 50K 342 865676.54
940 1298540.89
3 PLUS YEARS 1931-12-01 2012-03-01 WITH $ 50K 113 253743.64
3 PLUS YEARS 1931-12-01 2012-03-01 WITH $ 10K 475 463435.56
3 PLUS YEARS 1931-12-01 2012-03-01 WITH $ 1K 735 356765.64
1323 1073944.84
OTHER NULL NULL WITH $ 50K 375 575654.46
OTHER NULL NULL WITH $ 10K 153 453644.68
528 1029299.14

Vitor Montalvão

The query you posted doesn't have a GROUP BY clause. You can only use ROLLUP with GROUP BY clause.

zimmer9

ASKER

Cow you show me how to write the statement to achieve this result?

Vitor Montalvão

Imagine that you want to sum the acct:

SELECT MthTitl As [Date Range], DFrC as [From], DToC As [To], DollTitl As [Dollar Range],
SUM(CountOfCustomerNumber) As [Num Of Accts], SUM(SumOfAcctValue) As [Acct Value], MthF As [# Months], 
 DFr As [From Date ] 
FROM dbo.tblRange 
GROUP BY ROLLUP(MthTitl, DFrC, DToC, DollTitl, MthF, DFr)
ORDER By DFrC DESC, DollTitl DESC

Open in new window

zimmer9

ASKER

I end up with triplicate records and I think the Syntax is

GROUP BY MthTitl, DFrC, DToC, DollTitl, MthF, DFr WITH ROLLUP

Also the Dollar Ranges are losing their sorting order within each Date Range.

Now all the Dollar Ranges are being grouped together.

Scott Pletcher

ROLLUP would be nice here, but I'm not sure how to get it implemented. For now, it might be easier to UNION ALL the totals to the original detail rows, then sort them as needed:

SELECT
CASE WHEN is_total_only = 0 THEN [Date Range] ELSE '' END AS [Date Range],
CASE WHEN is_total_only = 0 THEN CONVERT(varchar(10), [From], 120) ELSE '' END AS [From],
CASE WHEN is_total_only = 0 THEN CONVERT(varchar(10), [To], 120) ELSE '' END AS [To],
CASE WHEN is_total_only = 0 THEN [Dollar Range] ELSE '' END AS [Dollar Range],
[Num Of Accts],
[Acct Value]
FROM (
SELECT
0 AS is_total_only,
MthTitl As [Date Range], DFrC as [From], DToC As [To], DollTitl As [Dollar Range],
CountOfCustomerNumber As [Num Of Accts], SumOfAcctValue As [Acct Value]
FROM #tblRange tr
UNION ALL
SELECT
1 AS is_total_only,
MthTitl AS [Date Range], MIN(DFrC) AS [From], CAST('19000101' AS date) AS [To], '' AS [Dollar Range],
SUM(CountOfCustomerNumber) AS CountOfCustomerNumber, SUM(SumOfAcctValue) AS SumOfAcctValue
FROM #tblRange
GROUP BY MthTitl
) AS derived
ORDER By derived.[From] DESC, is_total_only, derived.[Dollar Range]

zimmer9

ASKER

How would I resolve:

Type date is not a defined system type.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial