We help IT Professionals succeed at work.

Odd group by clause error

96 Views
Last Modified: 2019-02-26
Hi EE,

The SQL Query i have written:

SELECT
'DB_NAME' = db.name,
'FILE_NAME' = mf.name,
'FILE_TYPE' = mf.type_desc,
'FILE_PATH' = mf.physical_name,
'Size in MB' = CAST(((SUM(mf.size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM
sys.databases db
INNER JOIN sys.master_files mf
ON db.database_id = mf.database_id
Where DB.state = 0 
Group by  GROUPING SETS
              (
                     (db.name, Type_Desc),
                     (mf.size)
              )

Open in new window


Error: Column 'sys.master_files.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't understand how this error is occurring can someone assist any help is welcome.

Thank you.
Comment
Watch Question

SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ZackGeneral IT Goto Guy

Author

Commented:
Cheers Raja for your assistance.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Welcome, glad to assist!!