CODEWITHSUNDEEP
mysqlgroup-bymysql-error-1111
achillix
achillix

Reputation: 479

Get error #1111 - Invalid use of group function

I have a problem with this Query 

SELECT SUM(ROUND( SUM( note=3 ) / count( note=3 ) )) AS AVG, sma_famille.famille
                FROM sma_notes, sma_famille, sma_agents, sma_service_activite
                WHERE sma_famille.id_famille >=14
                AND sma_famille.id_service =9
                AND sma_notes.id_agent >=7
                GROUP BY sma_famille.id_famille

and i get the error message 

#1111 - Invalid use of group function

If somebody has experience with this kind of Query.

Here some details of the query:

I i take the query without the first SUM like this (This is the whole query Code)

    SELECT ROUND( SUM( note =3 ) / count( note =3 ) ) AS AVG, sma_famille.famille, sma_notes.id_agent
    FROM sma_notes, sma_famille, sma_agents, sma_service_activite
    WHERE sma_famille.id_famille >=14
    AND sma_famille.id_service =9
    AND sma_notes.id_agent >=7
    AND sma_notes.id_agent = sma_agents.id_agent
    AND sma_service_activite.id_activite = sma_notes.id_activite
    AND sma_service_activite.id_famille = sma_famille.id_famille
    GROUP BY sma_famille.id_famille, sma_notes.id_agent

Than i get results like:

    AVG | famille   | id_agent  
    3   |BEL        | 7 
    2   |BEL        | 8 
    1   |BEL        | 9 
    1   |CEL        | 7 
    2   |CEL        | 8 
    1   |CEL        | 9

But with my query who didn't works:

SELECT SUM(ROUND( SUM( note=3 ) / count( note=3 ) )) AS AVG, sma_famille.famille
    FROM sma_notes, sma_famille, sma_agents, sma_service_activite
        WHERE sma_famille.id_famille >=14
        AND sma_famille.id_service =9
        AND sma_notes.id_agent >=7
        AND sma_notes.id_agent = sma_agents.id_agent
        AND sma_service_activite.id_activite = sma_notes.id_activite
        AND sma_service_activite.id_famille = sma_famille.id_famille
        GROUP BY sma_famille.id_famille

i want to get this result:

    AVG | famille
    6   |BEL    
    4   |CEL

Thanks in advance

Achillix

Cheers

Upvotes: 1

Views: 5664

Answers (1)

Fahim Parkar
Fahim Parkar

Reputation: 31647

You should have used HAVING clause instead of WHERE.

See below.

Consider I have table Address with columns as City & State.

HAVING is used to check conditions after the aggregation takes place.

WHERE is used before the aggregation takes place.

This code:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City

Gives you a count of all the cities in MA.

This code:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City
Having Count(1)>5

Gives you the count of all the cities in MA that occur 6 or more times.

Also read

Definition, Comparison and Difference between HAVING and WHERE Clause

Where Vs Having.

Upvotes: 2

Related Questions