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

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
0
zimmer9
Asked:
zimmer9
  • 3
  • 2
  • 2
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The query you posted doesn't have a GROUP BY clause. You can only use ROLLUP with GROUP BY clause.
0
 
zimmer9Author Commented:
Cow you show me how to write the statement to achieve this result?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zimmer9Author Commented:
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.
0
 
Scott PletcherSenior DBACommented:
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]
0
 
zimmer9Author Commented:
How would I resolve:

Type date is not a defined system type.
0
 
Scott PletcherSenior DBACommented:
Sorry, change them to "datetime" rather than date.  I assumed you were in SQL 2008 compatibility level.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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