Adding columns you created in mysql?

Question

So say I have a mysql statement like this:

  SELECT username, @n := @n + 1 ranking, `1st places`, `2nd places`, `3rd places`, `top5`, `top3
FROM
(
SELECT username,
SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) `1st places`,
SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) `2nd places`,
SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) `3rd places`,
SUM(CASE WHEN rating < 6 THEN 5 ELSE 0 END) `top5`,
SUM(CASE WHEN rating < 4 THEN 5 ELSE 0 END) `top3`
FROM Table1
GROUP BY username
ORDER BY `1st places` DESC
) q, (SELECT @n := 0) n

How can I add up the columns that I created called, '1st places' + '2nd places' + '3rd places' in this sql statement and create another column called "total finishes"? Seems easy, but I cannot seem to figure it out.

I don't quite get it. rating < 4 overlaps with rating = 1 and so does partially with rating < 6. Can you give some example data with expected output so see what you want to do? — juergen d
– juergen d, Commented Jul 1, 2013 at 20:00
so what I want is a point system. Example: say someone places 1st aka "rating=1" then they get a point for being in 1st, then they also get 5 points for being in the top 5 (aka rating < 6) and also get 5 more points for being in the top 3 (aka rating < 4) so they would get a total of 11 points. — Mike Kochendorfer
– Mike Kochendorfer, Commented Jul 1, 2013 at 20:07
Im realizing how dumb this is actually now that I typed all of that, but I guess is there a way to do that? Even tho thinking about it, I dont need it lol. — Mike Kochendorfer
– Mike Kochendorfer, Commented Jul 1, 2013 at 20:08

Kickstart · Accepted Answer · 2013-07-01 20:11:07Z

1

You can just add them together and give an alias to that extra column:-

SELECT username, @n := @n + 1 ranking, `1st places`, `2nd places`, `3rd places`, `top5`, `top3, `1st places` + `2nd places` + `3rd places` AS `total finishes`
FROM
(
SELECT username,
SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) `1st places`,
SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) `2nd places`,
SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) `3rd places`,
SUM(CASE WHEN rating < 6 THEN 5 ELSE 0 END) `top5`,
SUM(CASE WHEN rating < 4 THEN 5 ELSE 0 END) `top3`
FROM Table1
GROUP BY username
ORDER BY `1st places` DESC
) q, (SELECT @n := 0) n

answered Jul 1, 2013 at 20:11

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rkz_io · Accepted Answer · 2013-07-01 20:10:57Z

I'm pretty sure that you can't re-use a field with a calculated value in the same SELECT query, however wrapping into an additional sub-query may be an option:

SELECT username, @n := @n + 1 ranking, `1st places`, `2nd places`, `3rd places`, `top5`, `top3
FROM
(
    SELECT
    *,
    `1st places`+`2nd places`+`3rd places` `total finishes`
    FROM (
        SELECT username,
        SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) `1st places`,
        SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) `2nd places`,
        SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) `3rd places`,
        SUM(CASE WHEN rating < 6 THEN 5 ELSE 0 END) `top5`,
        SUM(CASE WHEN rating < 4 THEN 5 ELSE 0 END) `top3`
        FROM Table1
        GROUP BY username
        ORDER BY `1st places` DESC
    )
)
q, (SELECT @n := 0) n

Haven't tried, still...

Collectives™ on Stack Overflow

Adding columns you created in mysql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related