ansaurus

Question

Answer 1

+1 A:

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

g.d.d.c 2010-07-21 03:16:42

So I need to use a temporary table? Any alternatives?

PC_Nerd 2010-07-21 03:23:14

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

g.d.d.c 2010-07-21 03:28:37

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?

PC_Nerd 2010-07-21 03:46:04

Answer 2

+2 A:

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)

Adam Bernier 2010-07-21 03:27:30

+1 This is a really good approach as well.

g.d.d.c 2010-07-21 03:37:22

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

PC_Nerd 2010-07-21 03:42:30

You don't need to use a column alias in the WHERE clause.

Adam Bernier 2010-07-21 03:49:31

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

Adam Bernier 2010-07-21 03:50:00

Answer 3

A:

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?

Brian Hooper 2010-07-21 09:21:48

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

PC_Nerd 2010-07-21 12:33:10

Righto. Sorry I couldn't be more help.

Brian Hooper 2010-07-21 12:44:28

ansaurus

tags:

views:

answers:

SQL - SUM of fields by alias

related questions