WHERE problem

[Baukep]

Hello,

In a WHERE clause I'm using: ....AND w_datum <=('2006-10-26') and it works.
But when I use: AND w_datum <= DATE_ADD(MAX(`wedstrijden`.`w_datum`), INTERVAL -7 DAY) it doesn't. GROUP BY error.

It is a query with unions (see below)

What am I doing wrong?

Thanks in advance

Bauke

SELECT therealselect.*,@row_index:=@row_index+1 Stand from (
SELECT w_seizoen AS Seizoen
, CONCAT(l_naam, ' (',speler,')') AS Naam
, COUNT(w_wnr) AS "Gespeeld"
, SUM(berpunten) AS "Tot. punten"
, SUM(berpunten)/COUNT(w_wnr) AS Winstperc
, ROUND(SUM(car)/SUM(w_brtn),4)- ROUND(temaken/25,4) AS Moyenne
FROM (
SELECT w_seizoen
, w_speler1 AS speler
, w_wnr
, IF(ROUND(w_s1_car/w_s1_temaken*100,3) >= 100 AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 1,
IF(ROUND(w_s1_car/w_s1_temaken*100,3) = ROUND(w_s2_car/w_s2_temaken*100,3), 1,
IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 3,
IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) < 100,2,0)))) AS berpunten
, w_s1_car AS car
, w_brtn
, w_s1_temaken AS temaken
FROM wedstrijden WHERE w_seizoen="2006/07" AND SUBSTRING(w_ronde,1,1)<>"G" AND w_datum <= ('2006-10-26')
UNION ALL
SELECT w_seizoen
, w_speler2 AS speler
, w_wnr
, IF(ROUND(w_s2_car/w_s2_temaken*100,3) >= 100 AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 1,
IF(ROUND(w_s2_car/w_s2_temaken*100,3) = ROUND(w_s1_car/w_s1_temaken*100,3), 1,
IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 3,
IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) < 100,2,0)))) AS berpunten
, w_s2_car AS car
, w_brtn
, w_s2_temaken AS temaken
FROM wedstrijden WHERE w_seizoen="2006/07" AND SUBSTRING(w_ronde,1,1)<>"G" AND w_datum <= ('2006-10-26')
) AS d
INNER JOIN leden ON l_id= d.speler
GROUP BY w_seizoen, speler
ORDER BY w_seizoen, Winstperc DESC, Moyenne DESC
) therealselect,(SELECT @row_index:=0) ri

 

[r937]

change this --

DATE_ADD(MAX(`wedstrijden`.`w_datum`), INTERVAL -7 DAY)

to this --

DATE_ADD(GREATEST(`wedstrijden`.`w_datum`), INTERVAL -7 DAY)

r937.com | rudy.ca

 

[Baukep]

Sorry,

Syntax error. I copied and pasted it and I don't see it

Bauke

 

[Baukep]

Sorry R937,

First reaction was a little fast and not so clear.

I tried your code and got an syntax error message with the last part of your code: ), INTERVAL -7 DAY)

I'm using MySQL 5.0.24a (XAMPP version) and I don't have a clue.

Any idea why I get this message?

Thanks

Bauke

 

[r937]

i guess my first reaction was too fast too

i thought that (`wedstrijden`.`w_datum`) was a comma-delimited list of two columns, whereas it's actually a single table.column

before version 3.something, you could use MAX where in more recent versions you have to use GREATEST

however, this isn't what's going on here at all

you can't use MAX because the subquery doesn't have a GROUP BY clause

r937.com | rudy.ca