CODEWITHSUNDEEP
mysqlmariadbmysql-error-1111
tanzeem
tanzeem

Reputation: 161

#1111 - Invalid use of group function in mysql

I have a table M_DAILY with fields

PS_DATE date,
tp int,
ep int,
mp int,

and have a working version of a user defined function nvl(x,y) which returns x if not null and y if x is null

My MySQL query is-

select sum(avg(case date_format(PS_DATE,'%Y') when '2005' then nvl(tp,0) else 0 end))tp, sum(avg(case date_format(PS_DATE,'%Y') when '2005' then nvl(ep,0) else 0 end)) ep, sum(avg(case date_format(PS_DATE,'%Y') when '2005' then nvl(mp,0) else 0 end)) mp
from M_DAILY
where PS_DATE >= date ('2005-01-01') and PS_DATE <= date ('2005-12-31')
group by PS_DATE;

I get the following error

#1111 - Invalid use of group function in mysql

Please help.

Upvotes: 2

Views: 6790

Answers (1)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

SUM(), COUNT(), AVG(), MIN(), MAX(), etc. are aggregate functions that requires you to specify a GROUP BY, unless you're using them on every column in your SELECT-list.

Remove Group By Clause

Try this

SELECT SUM(tp),SUM(ep),SUM(mp) FROM
(
    SELECT Avg(case date_format(PS_DATE,'%Y') when '2005' then nvl(tp,0) else 0 end) tp,   
           Avg(case date_format(PS_DATE,'%Y') when '2005' then nvl(ep,0) else 0 end) ep, 
           Avg(case date_format(PS_DATE,'%Y') when '2005' then nvl(mp,0) else 0 end) mp   
    FROM M_DAILY 
    WHERE PS_DATE >= date ('2005-01-01') and PS_DATE <= date ('2005-12-31');
) As T

Upvotes: 1

Related Questions