T-SQL: Aggregate Functions; Date Functions; and Group By Statement Woes

When combining Aggregate Functions with Date / Time functions within an SQL Query, you may run into errors relating to the “Group By” statement, such as:

  • “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”
  • “Invalid Column Name”
  • “Each GROUP BY expression must contain at least one column that is not an outer reference”

Here are some examples of how NOT to formulate the Query.

SELECT processed_by,
year(date_received) as year_received,
COUNT(date_sent) AS quotes_sent,
AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received,
AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required
FROM dbo.windows_quote
WHERE date_sent IS NOT NULL
GROUP BY processed_by
Msg 8120, Level 16, State 1, Line 2
Column ‘dbo.windows_quote.date_received’ is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY clause.

Excluding “year_received” from the GROUP BY statement does not work, and neither does adding it.

SELECT processed_by,
year(date_received) as year_received,
COUNT(date_sent) AS quotes_sent,
AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received,
AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required
FROM dbo.windows_quote
WHERE date_sent IS NOT NULL
GROUP BY processed_by, year_received
Msg 207, Level 16, State 1, Line 8
Invalid column name ‘year_received’.

I also tried to reference the field by index number..

SELECT processed_by,
year(date_received) as year_received,
COUNT(date_sent) AS quotes_sent,
AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received,
AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required
FROM dbo.windows_quote
WHERE date_sent IS NOT NULL
GROUP BY processed_by, 2
Msg 164, Level 15, State 1, Line 8
Each GROUP BY expression must contain at least one column that is not an outer reference.

Only by including the function itself within the GROUP BY statement could I get the Query to run properly.

UPDATE: The reason for this is because the “SELECT” clause is the very last part of the query to be evaluated. The query engine works through the statement as follows: FROM => WHERE => GROUP BY => SELECT. Therefore, the attribute “year_received” within the SELECT clause doesn’t exist when GROUP BY tries to reference it.

SELECT processed_by,
year(date_received) as year_received,
COUNT(date_sent) AS quotes_sent,
AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received,
AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required
FROM dbo.windows_quote
WHERE date_sent IS NOT NULL
GROUP BY processed_by, year(date_received)

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s