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

How to use the SQL Rollup statement in SQL Server 2005 based on particular table values?

I am creating a SQL Statement using SQL Server 2005. How would I rework the following SQL statement to get the following results? As to the values for the field MthF,
that field has values as follows:

when MthTitl = LESS THAN 6 MOS then MthF = 0
when MthTitl = 6 MOS TO 1 YR      then MthF = 6

select MthTitl, DFrC, DToC, DollTitl,
SUM (CtCust), SUM(SumAcct)
from tblRange
group by MthF, MthTitl, DFrC, DToC, DollTitl with ROLLUP
order by MthF, DollTitl Desc

MthTItl                                 DFrC            DToC             DollTitl             CtCust       SumAcct
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
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
6 MOS TO 1 YR                                                                                           2,912         170331.89  
GT                                                                                                                      6,608   738904583.78
0
zimmer9
Asked:
zimmer9
2 Solutions
 
Anthony PerkinsCommented:
Unfortunately, the non-ISO compliant syntax in SQL Server 2005 for WITH ROLLUP only support subtotals for all columns in the GROUP BY clause.  It would appear you only want subtotals for 2 out of 4 columns.

In SQL Server 2008 there is an ISO compliant syntax for WITH ROLLUP that should do it.
0
 
lwadwellCommented:
If you want to remove the sub-total rows for lower group by elements you will need to select them out and use a sub-query.  e.g.

select isnull(MthTitl,'GT'), DFrC, DToC, DollTitl, CtCust, SumAcct
from (select MthTitl, DFrC, DToC, DollTitl, SUM (CtCust) as CtCust, SUM(SumAcct) as SumAcct
      from tblRange 
      group by MthTitl, DFrC, DToC, DollTitl with ROLLUP)v
where MthTitl is null 
   or (DFrC is not null and DToC is not null and DollTitl is not null)
   or (MthTitl is not null and DFrC is null and DToC is null and DollTitl is null)
order by case when MthTitl = 'LESS THAN 6 MOS' then 0
              when MthTitl = '6 MOS TO 1 YR'   then 6
         end, DollTitl Desc

Open in new window

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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