unknown column in where... its defined right there!!!

Question

Hi guys,
im using this query

SELECT DISTINCT pr.competitorID AS compID, pr.age, CONCAT(pr.firstname, ' ', pr.lastname)AS name
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=1 AND participation_reports.competitorID=compID LIMIT 1) AS '100m'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=25 AND participation_reports.competitorID=compID LIMIT 1) AS '200m'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=37 AND participation_reports.competitorID=compID LIMIT 1) AS '400m'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=49 AND participation_reports.competitorID=compID LIMIT 1) AS '800m'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=61 AND participation_reports.competitorID=compID LIMIT 1) AS '1500m'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=67 AND participation_reports.competitorID=compID LIMIT 1) AS '3000m'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=69 AND participation_reports.competitorID=compID LIMIT 1) AS 'Javelin'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=81 AND participation_reports.competitorID=compID LIMIT 1) AS 'Shot Put'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=93 AND participation_reports.competitorID=compID LIMIT 1) AS 'Discus'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=105 AND participation_reports.competitorID=compID LIMIT 1) AS 'High Jump'
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=117 AND participation_reports.competitorID=compID LIMIT 1) AS 'Long Jump'
, SUM(pr.points) AS total
FROM participation_reports AS pr
JOIN event_data ON event_data.eventID=pr.heatEventID
WHERE event_data.minAge <= pr.age AND event_data.maxAge >= pr.age AND sex = 'F' AND total > 0 LIMIT 30

but im getting this error: 'undefined column "total" in "where clause"'

---

Update:
cheers guys,
anyone care to explain the difference between having and where (also aliased vs defined)

Answer 1

It's not defined, it's aliased, and you can't do it like that... try the HAVING clause:

FROM participation_reports AS pr 
JOIN event_data ON event_data.eventID=pr.heatEventID 
WHERE event_data.minAge <= pr.age AND event_data.maxAge >= pr.age AND sex = 'F'
HAVING SUM(pr.points) > 0 
LIMIT 30 

Answer 2

You need to look at the participation_reports table to make sure it's there. Your WHERE clause has it, but the table it's referring to might not. Check to see.

Answer 3

You cannot use the column alias in the where clause. You have to say where SUM(pr.points) > 0.

Unfortunately, you can't do that either. You have to say HAVING sum(pr.points) > 0.

Answer 4

you're getting bitten by SQL's brain-damaging syntax. consider SELECT, FROM, JOIN and WHERE as functions. then the semantics can be written as:

SELECT(
  WHERE(
    FROM(JOIN(R1, R2, COND))
  , COND
  )
, COLS
);

as you can see, your total column name is not in scope in the WHERE clause.