Avatar of Zack
Zack
Flag for Australia asked on

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.
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Raja Jegan R

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Zack

ASKER
Cheers Raja for your assistance.
Raja Jegan R

Welcome, glad to assist!!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck