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

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"'

cheers guys,

anyone care to explain the difference between having and where (also aliased vs defined)

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 

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.

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.

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.