Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58

    Unanswered: Invalid use of group function

    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
    ?>

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    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
    ?>

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •