April 13, 2015 at 8:52 am
Dear Folks,
While running the below query, getting the error:
Am I missing any of the columns to include in the SELECT column_list?
Msg 8120, Level 16, State 1, Line 1
Column 'sys.master_files.database_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
<code>select a.[Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
from
(
SELECT database_id,[Database Name]= DB_NAME(database_id),
[Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END ,
[Size in MB]= CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM sys.master_files
GROUP BY GROUPING SETS
(
(DB_NAME(database_id), Type_Desc),
(DB_NAME(database_id))
)
) as a
left join
(select max(last_user_update) as LastUserUpdate,database_id from sys.dm_db_index_usage_stats
group by database_id ) as b on a.database_id=b.database_id
</code>
Thanks.
April 13, 2015 at 8:55 am
group by database_id, not by the function applied to database_id.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 13, 2015 at 9:32 am
I'm with Gail.
Although, if you wanted to you could make this a derived table and then GROUP BY the function. But I don't think it will help your performance any. The opposite in fact.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 13, 2015 at 2:25 pm
In general, since you want to avoid grouping by varchar columns anywhere, as it's not very efficient, delay converting numeric codes until the outer-most query you can.
select DB_NAME(a.database_id) AS [Database Name],a.[Type],a.[Size in MB],b.LastUserUpdate
from
(
SELECT database_id,
[Type]= CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END ,
[Size in MB]= CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )
FROM sys.master_files
GROUP BY GROUPING SETS
(
(database_id, Type_Desc),
(database_id)
)
) as a
left join
(select max(last_user_update) as LastUserUpdate,database_id from sys.dm_db_index_usage_stats
group by database_id ) as b on a.database_id=b.database_id
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply