[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-20
2
Medium Priority
?
509 Views
Last Modified: 2012-08-21
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
Comment
Question by:zimmer9
2 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 38314691
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
 
LVL 25

Accepted Solution

by:
lwadwell earned 1000 total points
ID: 38315153
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question