Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    30

    Unanswered: SQL Server - GROUP BY clause

    SQL Server - GROUP BY clause

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

    I tried this query in dbms SQL Server 2008 and I have this output:
    Code:
    Q	MAT	NUMBER
    1F	MOL	103623
    1F	MOL	103623
    1D	LIP	69119
    1D	LIP	69119
    I need this:
    Code:
    Q	MAT	NUMBER
    1F	MOL	103623
    1D	LIP	69119
    	Tot	172742
    Can you help me?
    Thanks in advance.
    Code:
    SELECT
    	strDTZZ AS Q,
            COALESCE ([MAT], 'Tot') AS [MAT],
            NUMBER
    FROM
            (
    		SELECT
    			LEFT (DTZZ, 2) AS strDTZZ,
    			CASE
    		WHEN LEFT (TZZ, 2) = '1D' THEN
    			'LIP'
    		WHEN LEFT (TZZ, 2) = '1F' THEN
    			'MOL'
    		WHEN LEFT (TZZ, 2) = '1G' THEN
    			'IRT'
    		WHEN LEFT (TZZ, 2) = '1H' THEN
    			'MRE'
    		WHEN LEFT (TZZ, 2) = '1I' THEN
    			'UOT'
    		WHEN LEFT (TZZ, 2) = '1M' THEN
    			'MAL'
    		WHEN LEFT (TZZ, 2) = '1S' THEN
    			'RAS'
    		WHEN LEFT (TZZ, 2) = '1O' THEN
    			'PMC'
    		WHEN LEFT (TZZ, 2) = '1P' THEN
    			'BUP'
    		WHEN LEFT (TZZ, 2) = '1Q' THEN
    			'LAC'
    		ELSE
    			'CIS'
    		END AS MAT,
                    COUNT (*) AS NUMBER,
                    FROM
                            dbo_40
                    GROUP BY 
                    ROLLUP ( LEFT (DTZZ, 2),			
    			CASE
    		WHEN LEFT (TZZ, 2) = '1D' THEN
    			'LIP'
    		WHEN LEFT (TZZ, 2) = '1F' THEN
    			'MOL'
    		WHEN LEFT (TZZ, 2) = '1G' THEN
    			'IRT'
    		WHEN LEFT (TZZ, 2) = '1H' THEN
    			'MRE'
    		WHEN LEFT (TZZ, 2) = '1I' THEN
    			'UOT'
    		WHEN LEFT (TZZ, 2) = '1M' THEN
    			'MAL'
    		WHEN LEFT (TZZ, 2) = '1S' THEN
    			'RAS'
    		WHEN LEFT (TZZ, 2) = '1O' THEN
    			'PMC'
    		WHEN LEFT (TZZ, 2) = '1P' THEN
    			'BUP'
    		WHEN LEFT (TZZ, 2) = '1Q' THEN
    			'LAC'
    		ELSE
    			'CIS'
    		END
                    )
            ) AS SubQ
    WHERE
            1 = 1 
    AND (strDTZZ = '1D' OR strDTZZ = '1F');

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why would post this question here?

    you've been getting outstanding responses over at sitepoint -- SQL Server - GROUP BY clause

    are you not happy with the answers?

    are the answers you're getting for free not coming fast enough or something?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2012
    Posts
    30
    Because there are times that I don't understand the answers... Yourself have written this... I'm very happy to sitepoint and your help...
    nothing personal...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by cms9651 View Post
    I need this:
    Code:
    Q	MAT	NUMBER
    1F	MOL	103623
    1D	LIP	69119
    	Tot	172742
    (NUMBER in Tot) = (NUMBER in 1F) + (NUMBER in 1D)
    So, why not apply WHERE condition before grouping?

  5. #5
    Join Date
    Aug 2012
    Posts
    30
    thanks you for help, but I don't understand your reply.

Posting Permissions

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