SQL - SUM of fields by alias

I'm trying to find the sum of all the listed fields, but given that they are sub queries... I need to use aliases. If i use the listed aliases, I get column/field unknown errors... and if I attempt a sum(points) using a group by, I get an invalid use of group clause error.

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'
, (SELECT SUM(participation_reports.points) FROM participation_reports WHERE participation_reports.competitorID=compID) AS total
FROM participation_reports AS pr
JOIN event_data on even开发者_如何学JAVAt_data.eventID=pr.heatEventID
 WHERE event_data.minAge <= pr.age AND event_data.maxAge >= pr.age AND sex = 'F'
 AND total > 0
 LIMIT 30

Thanks for any ideas.

---

Give this a shot:

SELECT 
    pr.competitorID AS compID
    , pr.age 
    , CONCAT(pr.firstname, ' ', pr.lastname) AS name
    , SUM(CASE 
        WHEN pr.heatEventID=1 
        THEN pr.points 
        ELSE 0 END) AS "100m"
    , SUM(CASE 
        WHEN pr.heatEventID=25 
        THEN pr.points 
        ELSE 0 END) AS "200m"
    ...
    , SUM(CASE 
        WHEN pr.heatEventID IN (1,25,...)
        THEN pr.points
        ELSE 0 END) AS total
FROM 
    participation_reports pr
    JOIN event_data 
    ON event_data.eventID = pr.heatEventID
WHERE 
    ...
GROUP BY 
    pr.competitorID
    , pr.age
    , CONCAT(pr.firstname, ' ', pr.lastname)

---

Wrap that query in as a derived table and then select the sums.

---

I'm not quite clear on what you require, but if you want the sum of all those aliased columns, might your trouble not be that they are not legal column names? How about...

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 c_100m
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=25 AND participation_reports.competitorID=compID LIMIT 1) AS c_200m
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=37 AND participation_reports.competitorID=compID LIMIT 1) AS c_400m
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=49 AND participation_reports.competitorID=compID LIMIT 1) AS c_800m
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=61 AND participation_reports.competitorID=compID LIMIT 1) AS c_1500m
, (SELECT  participation_reports.points FROM participation_reports WHERE participation_reports.heatEventID=67 AND participation_reports.competitorID=compID LIMIT 1) AS c_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,
c_100m + c_200m + c_400m + c_800m + c_1500m + c_3000m + Javelin + Shot_Put + High_Jump + Long_Jump
, (SELECT SUM(participation_reports.points) FROM participation_reports WHERE participation_reports.competitorID=compID) 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

or have I misunderstood the problem?