Jørn Dahl-Stamnes wrote:
> I got the following query:
>
> select s.id,s.name,sum(p.fee) as fee from serie as s inner join race_serie as
> rs on (rs.serie_id=s.id) inner join races as r on (r.id=rs.race_id) inner
> join participants as p on (p.race_id=r.id) where s.receipt=1 and
> p.rider_id=236 and fee > 0 group by s.id order by s.f_date;
>
> which gives me the error:
>
> ERROR 1052 (23000): Column 'fee' in where clause is ambiguous
>
> Without the 'and fee > 0' the query works fine.
>
> mysql> select version();
> +----------------+
> | version() |
> +----------------+
> | 4.1.8-standard |
> +----------------+
>
> Is this due to an old version of MySQL?
No (though your version is quite old -- the current 4.1 is 4.1.21. A lot of
bugs were fixed in between
<http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html>.). As others have
pointed out, you have at least two things named fee, a column in participants
and a calculation in your select. Simply changing the alias of the latter won't
fix the problem though, as I'll explain below.
Gabriel PREDA wrote:
<snip>
> If you want the sum to be larger that zero then you would have to do:
>
> SELECT s.id, s.name, SUM(p.fee) AS fee
> FROM serie AS s
> INNER JOIN race_serie AS rs ON rs.serie_id = s.id
> INNER JOIN races AS r ON r.id = rs.race_id
> INNER JOIN participants AS p ON p.race_id = r.id
> WHERE s.receipt = 1
> AND p.rider_id = 236
> AND SUM(p.fee) > 0
> GROUP BY s.id
> ORDER BY s.f_date;
That won't work.
Jørn Dahl-Stamnes wrote:
<snip>
> What I want is to find out which series a given rider has participated where
> (s)he has paid fee for participating (in some cases a rider my participate
> without haveing paid).
>
> If the rider has paid fee once in a serie, then the sum will be more than 0.
> But will the query above give me the id and name for a serie where a rider
> has participated but not paid? One way to find out is to test it.
>
<snip>
> This gave the following error:
> ERROR 1111 (HY000): Invalid use of group function
Exactly.
Martin Jespersen wrote:
> Do the right thing(TM) and don't use an alias that is the same as a
> field that exists (this was mentioned before by someone else).
> Since there is a field in your join named "fee", using "fee" as an
> alias, is bound to cause your trouble.
> Also, trying to call sum(p.fee) twice is a waste of cputime. I say
> trying here because this is what fails, you are not allowed to call sum
> in your where clause, thus the "Invalid use of group function".
>
> try
>
> SELECT s.id, s.name, SUM(p.fee) AS fee_total
> FROM serie AS s
> INNER JOIN race_serie AS rs ON rs.serie_id = s.id
> INNER JOIN races AS r ON r.id = rs.race_id
> INNER JOIN participants AS p ON p.race_id = r.id
> WHERE s.receipt = 1
> AND p.rider_id = 236
> AND fee_total > 0
> GROUP BY s.id
> ORDER BY s.f_date;
That still won't work.
Whether you repeat the aggregate function in the WHERE clause or use its alias,
what you are asking for is unresolvable circular logic. The WHERE clause
determines which table rows are to be examined. You cannot determine whether or
not to look at a row based on the sum that will be produced if it is, or isn't,
included. Do you see the problem? You won't know the sum until after you have
chosen which rows are included in your results (the job of the WHERE clause),
but you are trying to exclude rows from the sum based on the eventual sum.
Until MySQL has been enhanced to accurately read your mind and predict the
future, that won't work.
What we really want is to sum the fees paid by a particular rider for each
series, then display only those series for which the total is positive. In
other words, we wish to filter the results *after* they have been calculated,
not before. That is the job of the HAVING clause. Try:
SELECT s.id, s.name, SUM(p.fee) AS fee_total
FROM serie AS s
INNER JOIN race_serie AS rs ON rs.serie_id = s.id
INNER JOIN races AS r ON r.id = rs.race_id
INNER JOIN participants AS p ON p.race_id = r.id
WHERE s.receipt = 1
AND p.rider_id = 236
GROUP BY s.id
HAVING fee_total > 0
ORDER BY s.f_date;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
