Odd group by clause error

Zack
Zack used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
For your query, there is no need for GROUPING SETS just a simple GROUP BY should be sufficient..
If you want the data to be formatted differently, kindly provide sample output so that we can help it out..
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 db.name,mf.name,mf.type_desc,mf.physical_name

Open in new window

ZackGeneral IT Goto Guy

Author

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

Commented:
Welcome, glad to assist!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial