SQL Server - GROUP BY clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Server - GROUP BY clause

Author

Topic

cms9651
Starting Member

28 Posts

Posted - 2012-08-11 : 11:22:04

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

I tried this query in dbms SQL Server 2008 and I have this output:


Q	MAT	NUMBER
1F	MOL	103623
1F	MOL	103623
1D	LIP	69119
1D	LIP	69119

I need this:


Q	MAT	NUMBER
1F	MOL	103623
1D	LIP	69119
	Tot	172742

Can you help me?
Thanks in advance.

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');

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-11 : 11:25:34

isnt it enough to add a DISTINCT?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cms9651
Starting Member

28 Posts

Posted - 2012-08-11 : 11:40:20

quote:
Originally posted by visakh16

isnt it enough to add a DISTINCT?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thank you for reply.
if add the DISTINCT in query I have this output:

Q 	MAT	NUMBER
1F	MOL	103623
1D	LIP	69119

I don't have the Total...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-11 : 16:43:58

isnt total returned by ROLLUP?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cms9651
Starting Member

28 Posts

Posted - 2012-08-12 : 16:23:16

quote:
Originally posted by visakh16

isnt total returned by ROLLUP?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Not, nothing total in my query last version...

vijayan.vinu3
Starting Member

19 Posts

Posted - 2012-08-18 : 07:41:56

Try this Query:


Select IsNULL(Q, '') As Q, MAX(Case When GroupingId = 1 Then 'Total' Else MAT End) As MAT, SUM(NUMBER) As NUMBER From
	(
		Select Q, MAX(MAT) As MAT, MAX(NUMBER) As NUMBER, GROUPING(Q) As GroupingId From Ex
		Group By Q
	) As a
Group By IsNULL(Q, '')
With RollUp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources