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?
精彩评论