1

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

Thanks for any ideas.

Improve this question
1

3 Answers 3

Reset to default
3

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

That solution doesnt seem to work, its based around the fact I cant use the column alias in the WHERE clause ( I think it parses it, such that aliases are the last things to be applied after said columns are searched, conditions matched and ordered etc...) I think ( I'm probably wrong), that you cant use column aliases anywhere other than defining them in the SELECT clause. EDIT: Specifically - I need the top SUM(points) rows..., and I dont want any rows where SUM(points) is 0 ( indicating no participation).
You don't need to use a column alias in the WHERE clause.
If you don't want SUM(points) = 0 then slap a HAVING clause on the end of that puppy; e.g. HAVING SUM(CASE WHEN pr.heatEventID IN 1,25,...) THEN pr.points ELSE 0 END) > 0.
1

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

Improve this answer

3 Comments

So I need to use a temporary table? Any alternatives?
@pc-nerd - More information about your schema would make it easier to refine the answer. Do you have any tables you could cross reference in a join to eliminate the need for all the sub-queries? How dependent are you on having those as columns? Could you get by with output grouping? Another option is to put this in a view and then query from it.
Ok... I can probably pastebin the relevent information if you would prefer that. The reason columsn are sub queries, is that each column is a sport, however that sport's event will change based on the age group/gender of the query ( this runs in a php loop over all age groups/genders)... hence the event's are specific to that sport/gender/age. The columns are definately required... How would a cross reference join eliminate them?
0

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?

Improve this answer

2 Comments

Hi, I tried your solution, but I still get an undefined c_100m column. I'm fairly certain from my reading that I cannot use a field alias in this context. *** I think my currently almost working solution is to just loop through using php and query each field, and take the sum based on another seperate query, twice as ineffient - but right not more time is being spent correcting this than getting my final table which is urgent.... thnx though
Righto. Sorry I couldn't be more help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.