Group by, what is that????

Muiter · #1 (**permalink**) 01-20-09, 16:06

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:

Quote:

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

guelphdad · #2 (**permalink**) 01-20-09, 22:06

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.

r937 · #3 (**permalink**) 01-20-09, 22:44

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?

Muiter · #4 (**permalink**) 01-21-09, 01:15

Thank you for the quick response.

I want an average of column bedrag_excl from table factuurnummers.

r937 · #5 (**permalink**) 01-21-09, 06:45

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

Muiter · #6 (**permalink**) 01-21-09, 07:41

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.

r937 · #7 (**permalink**) 01-21-09, 08:40

you understand GROUP BY now? great!!

what is your GROUP BY clause for the above query?

Muiter · #8 (**permalink**) 01-21-09, 09:13

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`.

r937 · #9 (**permalink**) 01-21-09, 09:32

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

Muiter · #10 (**permalink**) 01-21-09, 10:18

SELECT * FROM was there from the beginning when the was created by PHP Generator for MySQL, I changed the query where needed.

r937 · #11 (**permalink**) 01-21-09, 10:21

what is the primary key of the factuurnummers table?

Muiter · #12 (**permalink**) 01-21-09, 10:38

factuurnummer

r937 · #13 (**permalink**) 01-21-09, 10:40

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

mike_bike_kite · #14 (**permalink**) 01-21-09, 11:31

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.