Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58

    Unanswered: Group by, what is that????

    Thanks to this forum I'm able to build some bigger query's but I have one question, how do I add:
    Code:
    <?php
    AVG(t1.`bedrag_excl`)
      AS bedrag_excl_avg,
    ?>
    If I do now, this error comes up:
    Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
    This is my original query
    Code:
    <?php
    SELECT * FROM 
      (SELECT 
      DATEDIFF(IFNULL(t1.datum_betaald,NOW()),t1.datum_te_betalen) 
      AS 
      aantal_dagen,
      a.klantnaam,
      t1.`factuurnummer`, 
      t1.`offerte_id`, 
      lp1.`offerte` 
      AS 
      `lp_offerte_id`, 
      t1.`type`, 
      t1.`datum_verzonden`, 
      t1.`bedrag_excl`, 
      t1.`bedrag_incl`, 
      t1.`datum_te_betalen`, 
      t1.`datum_betaald` 
      FROM 
      `factuurnummers` 
      AS 
      t1 
      LEFT OUTER JOIN 
      `offertenummers` 
      AS 
      lp1 
      ON 
      (t1.`offerte_id` = lp1.`offerte`)
      LEFT OUTER JOIN 
        adressen AS a
        ON a.id = lp1.klant_id  
      WHERE NOW() < t1.datum_te_betalen
      AND
      t1.datum_betaald IS NULL
    ?>

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    when you use an aggregate function like AVG, MAX, MIN etc. you need a GROUP BY clause. a GROUP BY collapses like rows into a single row to do aggregate functions on.

    For instance if you have a school and you want the average mark for each class you would do:

    Code:
    SELECT
    classroom,
    AVG(marks)
    FROM
    schools
    GROUP BY
    classroom
    there are no aggregate functions on your larger query though so you won't need a group by clause.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, the only time you definitely need a GROUP BY clause is when you mix both aggregrate expressions (like SUM or AVG) along with non-aggregates (like columns) in the SELECT clause

    the "counter-example" is the classic count query --
    Code:
    SELECT COUNT(*) FROM daTable
    this does not have any non-aggregates in the SELECT clause, so it does not require a GROUP BY

    since you want to add AVG(t1.`bedrag_excl`) to your SELECT clause, you will have a mix of aggregates and non-aggregates, and therefore you will need a GROUP BY

    now, the big question is, you want the average calculated for what groups?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    Thank you for the quick response.

    I want an average of column bedrag_excl from table factuurnummers.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Muiter
    I want an average of column bedrag_excl from table factuurnummers.
    the overall average for the entire table? or an average for each group?

    the question is, what are the groups? based on what column values?'

    for example, suppose you had a table of students, and you wanted the average student grade -- do you want the average for all students in the table, or the average for students by class? by gender? by age? by age and gender? ...

    do you see the question i am asking?

    your query will need a GROUP BY clause, and i'm asking about what should be the columns in the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    Ok r937, I now understand what you mean.

    For one of my pages I want
    Code:
    WHERE NOW() < t1.datum_te_betalen
        AND
        t1.datum_betaald IS NULL
    but that is already in my query.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you understand GROUP BY now? great!!

    what is your GROUP BY clause for the above query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    I I understand it right I'm not sure if I need an Group by, altough the error does say it.

    With the top query I get an certain result because of the where clause and I just want an avareage of this query from column t1.`bedrag_excl`.
    Last edited by Muiter; 01-21-09 at 10:18.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i really don't understand what you're trying to get, but i'm pretty sure you do not understand GROUP BY

    let's go back to the original query

    why do you have SELECT * FROM ( subquery ) ?

    i mean, what is the reason for using SELECT * FROM... ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    SELECT * FROM was there from the beginning when the was created by PHP Generator for MySQL, I changed the query where needed.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is the primary key of the factuurnummers table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    factuurnummer

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, try this --
    Code:
    SELECT DATEDIFF(IFNULL(t1.datum_betaald,NOW())
                  ,t1.datum_te_betalen) AS aantal_dagen
         , a.klantnaam
         , t1.factuurnummer
         , AVG(t1.bedrag_excl) AS bedrag_excl_avg
         , t1.offerte_id
         , lp1.offerte AS lp_offerte_id
         , t1.type
         , t1.datum_verzonden
         , t1.bedrag_excl
         , t1.bedrag_incl
         , t1.datum_te_betalen
         , t1.datum_betaald 
      FROM factuurnummers AS t1 
    LEFT OUTER 
      JOIN offertenummers AS lp1 
        ON lp1.offerte = t1.offerte_id
    LEFT OUTER 
      JOIN adressen AS a
        ON a.id = lp1.klant_id  
     WHERE NOW() < t1.datum_te_betalen
       AND t1.datum_betaald IS NULL
    GROUP
        BY t1.factuurnummer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Muiter,

    Why not just buy Rudy's book, I believe it's aimed at folks like yourself, and then learn SQL at your own pace. The book link is in his signature. If money's tight then you can find loads of learn SQL sites on the web.

  15. #15
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    Quote Originally Posted by r937
    okay, try this
    Okay, that works (I think), it does in phpmyadmin but the result when in php doesn't show up.
    Code:
    <?php echo $bedrag_excl_avg ?>
    What if I have an table without an primary?

Posting Permissions

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