SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    SQL SERVER Rollup Clause

    Hi there, I need your help.
    Here is my problem.

    I tried this query in dbms SQL Server 2008 and not have error.
    I use in this query the syntax GROUP WITH ROLLUP (...) http://blog.sqlauthority.com/2010/02...rollup-clause/ and I have this output:
    Code:
    MAT	myNumber
    MAO	172742
    MAE	157907
    MAC	341314
    MAS	341510
            1013473
    I need instead this other output:
    Code:
    MAT	myNumber
    MAO	172742
    MAE	157907
    MAC	341314
    MAS	341510
    Tot	1013473
    Can you help me?
    Thanks in advance.

    Code:
    SELECT
            [MAT],
    	[myNumber]
    FROM
            (
                    SELECT
    		COUNT (*) AS [myNumber],
                            CASE
                    WHEN LEFT (TZZ, 2) = 'DD'
                    OR LEFT (TZZ, 2) = 'DF' THEN
                            'MAO'
                    WHEN LEFT (TZZ, 2) = 'DG'
                    OR LEFT (TZZ, 2) = 'DH' THEN
                            'MAE'
                    WHEN LEFT (TZZ, 2) = 'DI'
                    OR LEFT (TZZ, 2) = 'DM'
                    OR LEFT (TZZ, 2) = 'DS'
                    OR LEFT (TZZ, 2) = 'DO' THEN
                            'MAC'
                    WHEN LEFT (TZZ, 2) = 'DP'
                    OR LEFT (TZZ, 2) = 'DQ'
                    OR LEFT (TZZ, 2) = 'DR' THEN
                            'MAS'
    		ELSE
    			'Tot'
                    END AS [MAT]
                    FROM
                            dbo_40
                    GROUP BY
    		ROLLUP (
                            CASE
                    WHEN LEFT (TZZ, 2) = 'DD'
                    OR LEFT (TZZ, 2) = 'DF' THEN
                            'MAO'
                    WHEN LEFT (TZZ, 2) = 'DG'
                    OR LEFT (TZZ, 2) = 'DH' THEN
                            'MAE'
                    WHEN LEFT (TZZ, 2) = 'DI'
                    OR LEFT (TZZ, 2) = 'DM'
                    OR LEFT (TZZ, 2) = 'DS'
                    OR LEFT (TZZ, 2) = 'DO' THEN
                            'MAC'
                    WHEN LEFT (TZZ, 2) = 'DP'
                    OR LEFT (TZZ, 2) = 'DQ'
                    OR LEFT (TZZ, 2) = 'DR' THEN
                            'MAS'
    		ELSE
    			'Tot'
                    END
    		)
            ) AS SubQ
    WHERE
            1 = 1
    ORDER BY
        CASE MAT
            WHEN 'MAO' THEN 1
            WHEN 'MAE' THEN 2
            WHEN 'MAC' THEN 3
            ELSE 4
        END;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try using COALESCE([MAT],'Total') AS [MAT] in the outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try using COALESCE([MAT],'Total') AS [MAT] in the outer query
    Thanks a lot!!!!
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •