Odd group by clause error

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.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZackGeneral IT Goto GuyAuthor Commented:
Cheers Raja for your assistance.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to assist!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.