Unknown column 'result1' in 'field list'

Question

i am trying to sum the values for result1 and result2 but it comes up with unknown column result1. Can someone please assist. What am i doing wrong.

SELECT *
      ,case when event1 = '100mh' then 25.4347*power(sum(18.00-10.40),1.81) end as result1
      ,case when event2 = 'highjump' then 9.4347*power(sum(14.00-145),1.835) end as result2
      ,SUM(result1 + result2 )as total
from heptathlon 
group by athlete_id

You should mark off past questions that were resolved before posting new ones, IMHO. — Funk Forty Niner, Oct 26 '17 at 12:16
Check this https://stackoverflow.com/questions/22369336/mysql-sum-multiple-columns — Bharat, Oct 26 '17 at 12:16

XING · Accepted Answer · 2017-10-26T13:30:25.060

2

Try this:

SELECT t.*, sum(t.result1 + t.result2) AS total
  FROM (  SELECT b.*,
                 CASE
                    WHEN b.event1 = '100mh'
                    THEN
                       25.4347 * POWER (SUM (18.00 - 10.40), 1.81)
                 END
                    AS result1,
                 CASE
                    WHEN b.event2 = 'highjump'
                    THEN
                       9.4347 * POWER (SUM (14.00 - 145), 1.835)
                 END
                    AS result2
            FROM heptathlon b
        GROUP BY b.athlete_id) t

edited Oct 26 '17 at 13:30

answered Oct 26 '17 at 13:25

XING

9,608
4
22
38

Your solution results in the following error. FUNCTION framework.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual – Webjunkie Oct 26 '17 at 13:29
@Webjunkie sum keyword was missing.. Check now. It should work in Mysql however the earlier one will also work fine in oracle. – XING Oct 26 '17 at 13:30
your solution worked. I just needed to remove the unwanted spaces. Thanks – Webjunkie Oct 26 '17 at 13:33

Unknown column 'result1' in 'field list'

1 Answers1