Invalid use of group function

Muiter · #1 (**permalink**) 06-30-09, 16:15

I'm getting this error Invalid use of group function can anyone see what is wrong, I think it's in the SUM part.

Code:

<?php
SELECT
    					 		  ROUND(SUM(COUNT(ordnr.ordernummer) / COUNT(off.offerte) * 100),2) AS score,
    					 		  off.klant_id AS off_klant_id,
    					 		  adr.klantnaam AS adr_klantnaam
						  		  FROM ordernummers AS ordnr
						  		  LEFT JOIN offertenummers AS off
            					  ON off.offerte = ordnr.offerte_id
						  		  LEFT JOIN adressen AS adr 
            					  ON adr.id = off.klant_id
           						  LEFT JOIN factuurnummers AS fac
            					  ON fac.offerte_id = off.offerte 
						  		  GROUP BY off_klant_id
						  		  ORDER BY score DESC
						  		  LIMIT 20
?>

r937 · #2 (**permalink**) 06-30-09, 16:49

the problem is here --

Code:

SUM(COUNT(...

that's an invalid use of a group function

in other words, you cannot sum a count, you can only do one level of aggregation in any grouping

however, it ~is~ possible to get sums of counts, but you have to use nested queries for that

Muiter · #3 (**permalink**) 06-30-09, 17:24

Yes, I noticed it when I was closing my eyes for an minute while think it over again.

Here is an other one.
Here I want an average of 2 dates per customer_id (klant_id)
Any suggestions?

Code:

<?php
SELECT
    					 		  ROUND(SUM(DATEDIFF(off.datum_offerte,ordnr.datum_order))/(COUNT(ordnr.ordernummer),1) AS aantal_dagen,
    					 		  off.klant_id AS off_klant_id,
    					 		  adr.klantnaam AS adr_klantnaam
						  		  FROM offertenummers AS off
						  		  LEFT JOIN adressen AS adr 
            					  ON adr.id = off.klant_id             					  
            					  LEFT JOIN ordernummers AS ordnr
            					  ON ordnr.offerte_id = off.offerte 
						  		  GROUP BY off_klant_id
						  		  ORDER BY fac_totaal DESC
						  		  LIMIT 20
?>

guelphdad · #4 (**permalink**) 06-30-09, 23:07

you might want to check the formatting of your code, here's a link to an sql code formatter:
Instant SQL Formatter

I didn't bother to read your posts, others might not either.

just a friendly tip for future use.

Internet Services

Web Development

Digital Marketing

Consumer Tech