SQL Server - GROUP BY clause

**cms9651** · Aug 11, 2012 03:25

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

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

[Err] 42S22 - [SQL Server] Invalid column name 'TZZ';

If replace in my query the LEFT (TZZ, 2) with TZZ and replace in GROUP BY clause LEFT (TZZ, 2) with TZZ, query working but not group by LEFT (TZZ, 2).

I need group the output with LEFT (TZZ, 2).

Can you help me?
Thanks in advance.

Code:

SELECT
	COALESCE ([MAT], 'Tot') AS [MAT]
FROM
        (
		SELECT
			LEFT (TZZ, 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 MAT,
                FROM
                        dbo_40
                GROUP BY LEFT (TZZ, 2),
                ROLLUP (			
			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 (
LEFT (TZZ, 2) = '1D'
OR LEFT (TZZ, 2) = '1F'
)

**r937** · Aug 11, 2012 04:45

try giving LEFT(TZZ,2) a column alias in your inner query, then use that in your outer query's WHERE clause

**cms9651** · Aug 11, 2012 07:13

Originally Posted by r937

try giving LEFT(TZZ,2) a column alias in your inner query, then use that in your outer query's WHERE clause

Thank you.
I tried your suggestion, but 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?
Query:

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

**r937** · Aug 11, 2012 07:15

the reason that your 'Tot' row is not showing up is because you specifically excluded it with this line --

Code:

AND (strDTZZ = '1D' OR strDTZZ = '1F');

do you understand why?

**cms9651** · Aug 11, 2012 07:21

Thanks.
Yes I understand, but if remove this condition WHERE in the query:

Code:

AND (strDTZZ = '1D' OR strDTZZ = '1F');

this is the output:

Code:

Q	MAT	NUMBER
1F	MOL	103623
1F	MOL	103623
1D	LIP	69119
1D	LIP	69119
1H	MRE	86788
1H	MRE	86788
1G	IRT	71119
1G	IRT	71119
1O	PMC	120600
1O	PMC	120600

**r937** · Aug 11, 2012 08:51

no, i don't think you understand

if you want only '1D' and '1F' in your results, then you must keep that WHERE clause

but if you also want the Tot line, then you must also code for it in the WHERE clause

**cms9651** · Aug 11, 2012 09:13

thank you.

You have right: I don't understand.

I tried this version, but I have error in subquery:

Code:

[Err] 42000 - [SQL Server] Column 'SubQs.MAT' is invalid in the select
list because it is not contained in either an aggregate function
or the GROUP BY clause.

With add DISTINCT I have this output without Total:

Code:

Q 	MAT	NUMBER
1F	MOL	103623
1D	LIP	69119

Can you help me?
Thanks in advance

Code:

SELECT
	strTZZ AS Q,
        COALESCE ([MAT], 'Tot') AS [MAT],
        NUMBER
FROM
        (
		SELECT DISTINCT
			LEFT (TZZ, 2) AS strTZZ,
			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 (TZZ, 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 SubQs
WHERE
	1 = 1
AND (strTZZ = '1D' OR strTZZ = '1F')
GROUP BY
	ROLLUP (
		CASE
		WHEN LEFT (strTZZ, 2) = '1D' THEN
			'LIP'
		WHEN LEFT (strTZZ, 2) = '1F' THEN
			'MOL'
		WHEN LEFT (strTZZ, 2) = '1G' THEN
			'IRT'
		WHEN LEFT (strTZZ, 2) = '1H' THEN
			'MRE'
		WHEN LEFT (strTZZ, 2) = '1I' THEN
			'UOT'
		WHEN LEFT (strTZZ, 2) = '1M' THEN
			'MAL'
		WHEN LEFT (strTZZ, 2) = '1S' THEN
			'RAS'
		WHEN LEFT (strTZZ, 2) = '1O' THEN
			'PMC'
		WHEN LEFT (strTZZ, 2) = '1P' THEN
			'BUP'
		WHEN LEFT (strTZZ, 2) = '1Q' THEN
			'LAC'
		ELSE
			'CIS'
		END
	)

**r937** · Aug 11, 2012 10:04

try this --

Code:

SELECT strtzz 
     , COALESCE(mat,'Tot') AS strmat
     , number
  FROM ( SELECT strtzz
              , mat
              , COUNT (*) AS number
           FROM ( SELECT LEFT(tzz,2) AS strtzz
                       , 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
                    FROM dbo_40 ) AS data
         GROUP 
            BY strtzz
             , ROLLUP ( mat )
       ) AS SubQs
 WHERE 1 = 1
   AND ( 
       strtzz IN ('1D','1F')
    OR mat IS NULL
       )

**cms9651** · Aug 11, 2012 10:25

thank you very much for help.

I tried your last query, but this is the output:

Code:

strtzz	strmat	number
1D	LIP	69119
1D	Tot	69119
1F	MOL	103623
1F	Tot	103623
1G	Tot	71119
1H	Tot	86788
1I	Tot	87213
1M	Tot	95975
1O	Tot	120600
1P	Tot	96746
1Q	Tot	64213
1R	Tot	180551
1S	Tot	37526
LG	Tot	1568

**r937** · Aug 11, 2012 11:10

Code:

SELECT strtzz
     , COALESCE(mat,'Tot') AS strmat
     , COUNT(*) AS number
  FROM ( SELECT LEFT(tzz,2) AS strtzz
              , 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
           FROM dbo_40 ) AS data
          WHERE 1 = 1
            AND LEFT(tzz,2) IN ('1D','1F')                    
GROUP 
   BY strtzz
    , ROLLUP ( mat )

**cms9651** · Aug 11, 2012 11:20

thank you very much... we are near ...

Code:

strtzz	strmat	number
DD	LIP	69119
DD	Tot	69119
DF	MOL	103623
DF	Tot	103623

Instead of:

Code:

strtzz	strmat	number
DD	LIP	69119
DF	MOL	103623
	Tot	172742

**r937** · Aug 11, 2012 11:52

change

Code:

GROUP 
   BY strtzz
    , ROLLUP ( mat )

to

Code:

GROUP 
   BY ROLLUP ( strtzz )
    , mat

**cms9651** · Aug 11, 2012 11:55

Code:

strtzz	strmat	number
1F	MOL	103623
	MOL	103623
1D	LIP	69119
	LIP	69119

**r937** · Aug 11, 2012 15:59

how many different combinations are you going to get me to suggest before you take the reins and start testing them yourself, eh

Code:

GROUP BY ROLLUP ( strtzz , mat )

each of the combinations you have seen produced different types of subtotals, correct?

my guess is, you only want grand totals

so you could (1) keep experimenting, possibly including a HAVING clause to eliminate the subtotals and keep only the grand total, or (2) forget ROLLUP and do the grand total in your front end application

**cms9651** · Aug 12, 2012 13:21

Originally Posted by r937

how many different combinations are you going to get me to suggest before you take the reins and start testing them yourself, eh

You have right, first of all I must say that I am a newbie when it comes to SQL Server 2008.
Ask you to be patient with me, I want to learn...

Your help would be very appreciated. Thanks in advance for your time and hints.

Code:

GROUP BY ROLLUP ( strtzz , mat )

each of the combinations you have seen produced different types of subtotals, correct?

This is the output with last modification:

Code:

strtzz	strmat	number
1D	LIP	69119
1D	Tot	69119
1F	MOL
1F	Tot	103623
	Tot	172742

my guess is, you only want grand totals

so you could (1) keep experimenting, possibly including a HAVING clause to eliminate the subtotals and keep only the grand total, or (2) forget ROLLUP and do the grand total in your front end application

I have fix the problem using the method INSERT INTO SELECT.
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose. (http://blog.sqlauthority.com/2007/08...ct-into-table/)

Before method INSERT INTO SELECT, I tried this code with UNION syntax and I have my output:

Code:

strmat	number
mol	103623
lip	69119
Tot	172742

I'm interested in your opinion please.

Code:

USE MySQLServerDB
GO
----Create TestTable
CREATE TABLE TestTable (
	[MAT] VARCHAR (100),
	[NUMBER] INT
) 

----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (
	[MAT],
	[NUMBER]
) SELECT
	COALESCE ([MAT], 'Tot') AS [MAT],
	[NUMBER]
FROM
	(
		SELECT DISTINCT
                        LEFT (TZZ, 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
	WHERE
		1 = 1
	                GROUP BY 
                ROLLUP ( LEFT (TZZ, 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 LEFT(tzz,2) IN ('1D','1F');

 ----Verify that Data in TestTable
SELECT
	[MAT],
	[NUMBER]
FROM
	TestTable
UNION
	SELECT
		COALESCE ([MAT], 'Totale') AS [MAT],
		SUM ([NUMBER])
	FROM
		TestTable
	GROUP BY
		ROLLUP (mat);

----Clean Up Database
DROP TABLE TestTable
GO

**r937** · Aug 12, 2012 16:14

Originally Posted by cms9651

I'm interested in your opinion please.

if it produces what you want, it is good

**cms9651** · Aug 13, 2012 00:15

thanks a lot!

User Tag List

Thread: SQL Server - GROUP BY clause

Thread Tools

Display

SQL Server - GROUP BY clause

Bookmarks

Bookmarks

Posting Permissions