SQL Server - GROUP BY clause

cms9651 · August 11, 2012, 10:25am

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.


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 · August 11, 2012, 11:45am

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

cms9651 · August 11, 2012, 2:13pm

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

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 · August 11, 2012, 2:15pm

the reason that your ‘Tot’ row is not showing up is because you specifically excluded it with this line –

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

do you understand why?

cms9651 · August 11, 2012, 2:21pm

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


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

this is the output:


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 · August 11, 2012, 3:51pm

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 · August 11, 2012, 4:13pm

thank you.

You have right: I don’t understand.

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

[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:

Q 	MAT	NUMBER
1F	MOL	103623
1D	LIP	69119

Can you help me?
Thanks in advance


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 · August 11, 2012, 5:04pm

try this –


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 · August 11, 2012, 5:25pm

thank you very much for help.

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

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 · August 11, 2012, 6:10pm

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 · August 11, 2012, 6:20pm

thank you very much… we are near …

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

Instead of:

strtzz	strmat	number
DD	LIP	69119
DF	MOL	103623
	Tot	172742

r937 · August 11, 2012, 6:52pm

change

GROUP 
   BY strtzz
    , ROLLUP ( mat )

to

GROUP 
   BY ROLLUP ( strtzz )
    , mat

cms9651 · August 11, 2012, 6:55pm

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

r937 · August 11, 2012, 10:59pm

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


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 · August 12, 2012, 8:21pm

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.


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:

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/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/)

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

strmat	number
mol	103623
lip	69119
Tot	172742

I’m interested in your opinion please.

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 · August 12, 2012, 11:14pm

if it produces what you want, it is good

cms9651 · August 13, 2012, 7:15am

thanks a lot!