CODEWITHSUNDEEP
mysqlsql
user3108296
user3108296

Reputation:

Invalid Use of a Group Function

I have been looking for a solution to this seemingly simple problem. The query only breaks when I introduce the last sum() function in the where clause. I get the error "Error Code: 1111. Invalid use of group function." I can't figure out why it won't allow me to make this one where statement.

Query

select 
    dIntervalStart,
    weekofyear(dIntervalStart) WeekOfYear,
    weekday(dIntervalStart) DayOfWeek,
    cast(dIntervalStart as time) IntervalStart,
    sum(nExternToInternAcdCalls) CallVolume
from
    iwrkgrpqueuestats
where
    SiteId = 1
        and cname in ('applications' , 'employer',
        'factfind',
        'general',
        'other')
        and cReportGroup = '*'
        and CAST(dIntervalStart as time) between '07:00' and '17:30'
        and weekday(dIntervalStart) not in (5 , 6)
        and sum(nExternToInternAcdCalls) <> 0
group by weekofyear(dIntervalStart) , weekday(dIntervalStart) , cast(dIntervalStart as time)
order by IntervalStart asc, dIntervalStart desc

Upvotes: 1

Views: 763

Answers (2)

spencer7593
spencer7593

Reputation: 108370

Relocate the predicate on the aggregate expression to the HAVING clause, e.g.:

 GROUP BY ...
HAVING sum(nExternToInternAcdCalls) <> 0
 ORDER BY ...

The predicates in the WHERE clause are evaluated when rows are accessed, they pick out which rows are included. The value of the aggregate expression (e.g. SUM(foo)) isn't available when the rows are accessed. The value for that expression can't be determined until after the rows are accessed.

The predicates in the HAVING clause are applied after the rows are accessed, and after the resultset is prepared. When the HAVING predicates are evaluated, the aggregate (SUM(foo)) will be available.

NOTE: the GROUP BY clause would typically include all of the non-aggregate expressions in the SELECT list. MySQL is more relaxed than other databases, which can be both a blessing and a curse; if the GROUP BY "collapses" rows, for non-aggregates in the SELECT list, MySQL returns a value from one of the rows.

That is, if you GROUP BY weekofyear(foo), and return foo in the SELECT list, and there are multiple rows with foo values that evaluate to the same weekofyear(foo) value, MySQL will return one row for the given weekofyear(foo) and also return one of the foo values from one of the rows. Other databases (Oracle, SQL Server, et al.) would throw an error rather than returning a resultset.

Upvotes: 2

Ryx5
Ryx5

Reputation: 1366

You cannot use group by like this, group by is used to distinct result and and specify the element of reference of aggregate functions

Try this :

select 
    dIntervalStart,
    weekofyear(dIntervalStart) WeekOfYear,
    weekday(dIntervalStart) DayOfWeek,
    cast(dIntervalStart as time) IntervalStart,
    sum(nExternToInternAcdCalls) CallVolume
from
    iwrkgrpqueuestats
where
    SiteId = 1
        and cname in ('applications' , 'employer',
        'factfind',
        'general',
        'other')
        and cReportGroup = '*'
        and CAST(dIntervalStart as time) between '07:00' and '17:30'
        and weekday(dIntervalStart) not in (5 , 6)
        and sum(nExternToInternAcdCalls) <> 0
group by dIntervalStart,
        WeekOfYear,
        DayOfWeek,
        IntervalStart --columns not using aggregate functions
order by IntervalStart asc, dIntervalStart desc

Upvotes: 0

Related Questions