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] Incorrect query output

    Hello there, I hope your help.
    This query working but I have problem with presentation output.
    Now the output is:
    Code:
    MAT	DVD	SALES
    MAC	L21	24
    MAS	L21	276
    MAC	L20	165
    Tot	L20	411
    Tot	L21	300
    MAN	L20	80
    MAO	L20	81
    MAS	L20	85
    Instead I need this different output:
    Code:
    MAT	DVD	SALES
    MAC	L21	24
    MAS	L21	276
    Tot	L21	300
    MAC	L20	165
    MAN	L20	80
    MAO	L20	81
    MAS	L20	85
    Tot	L20	411
    Can you help me?
    Thank you in advance
    Code:
    SELECT
    	COALESCE ([MAT], 'Tot') AS [MAT],
    	[DVD],
    	[SALES]
    FROM
    	(
    		SELECT DISTINCT
    			[DVD],
    			CASE
    		WHEN LEFT (TZZ, 2) = '1D'
    		OR LEFT (TZZ, 2) = '1F' THEN
    			'MAO'
    		WHEN LEFT (TZZ, 2) = '1G'
    		OR LEFT (TZZ, 2) = '1H' THEN
    			'MAN'
    		WHEN LEFT (TZZ, 2) = '1I'
    		OR LEFT (TZZ, 2) = '1M'
    		OR LEFT (TZZ, 2) = '1S'
    		OR LEFT (TZZ, 2) = '1O' THEN
    			'MAC'
    		WHEN LEFT (TZZ, 2) = '1P'
    		OR LEFT (TZZ, 2) = '1Q'
    		OR LEFT (TZZ, 2) = '1R' THEN
    			'MAS'
    		ELSE
    			'Tot'
    		END AS [MAT],
    		COUNT (*) AS [SALES]
    	FROM
    		dbo_40
    	WHERE
    		1 = 1
    	AND [TZZ] NOT LIKE 'LG%'
    	GROUP BY
    		[DVD],
    		CASE
    	WHEN LEFT (TZZ, 2) = '1D'
    	OR LEFT (TZZ, 2) = '1F' THEN
    		'MAO'
    	WHEN LEFT (TZZ, 2) = '1G'
    	OR LEFT (TZZ, 2) = '1H' THEN
    		'MAN'
    	WHEN LEFT (TZZ, 2) = '1I'
    	OR LEFT (TZZ, 2) = '1M'
    	OR LEFT (TZZ, 2) = '1S'
    	OR LEFT (TZZ, 2) = '1O' THEN
    		'MAC'
    	WHEN LEFT (TZZ, 2) = '1P'
    	OR LEFT (TZZ, 2) = '1Q'
    	OR LEFT (TZZ, 2) = '1R' THEN
    		'MAS'
    	ELSE
    		'Tot'
    	END WITH ROLLUP
    	) AS SubQs
    WHERE
    	1 = 1
    AND (
    	[DVD] IN ('L20', 'L21')
    );
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Add

    Code:
     order
        by DVD desc,
           case when MAT is null then 1 else 0 end

  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 swampBoogie View Post
    Add

    Code:
     order
        by DVD desc,
           case when MAT is null then 1 else 0 end
    many thanks!
    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
  •