mysql : calculations on alias fields in mysql

Question

I already referred :

&

I want to some calculations on alias fields but it throws error

following is my query

 select j.*,
    (select sum(stars) from ratingstar where jobid=j.id) as stars,
    (select count(*) from ratingstar where jobid=j.id) as count,
    ((stars/(count*5)*5)) as rating     //I have problem here if I remove this it works fine 
from jobs j inner join proposals p on p.jobid=j.id 
            inner join us_signup u on u.id=p.userid 
            inner join hired h on h.proposalid=p.id 
            where h.status="finished"

But it throws error

error in this select line

select j.*,
    (select sum(stars) from ratingstar where jobid=j.id) as stars,
    (select count(*) from ratingstar where jobid=j.id) as count,
    ((stars/(count*5)*5)) as rating     //I have problem here if I remove this it works fine

and error is

Fatal error: Uncaught exception 'Exception' with message 'Unknown column 'stars' in 'field list' query: select j.,(select sum(stars) from ratingstar where jobid=j.id) as stars,(select count() from ratingstar where jobid=j.id) as count,((stars/(count*5)*5)) as rating from jobs j inner join proposals p on p.jobid=j.id inner join us_signup u on u.id=p.userid inner join hired h on h.proposalid=p.id where h.status="finished"' in E:\wamp\www\sugumar\mysuite\includes\classes\MysqliDb.php on line 3637

Just use [`avg(stars)`](https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_avg). — Phylogenesis, Jun 12 '17 at 13:52
`(select avg(stars) from ratingstar where jobid=j.id) average` — Phylogenesis, Jun 12 '17 at 13:57

frankiehf · Answer 1 · 2017-06-12T13:52:32.363

0

i think you missed a ) at the end

((stars/(count*5)*5))

edit* you didnt miss it,you just added an useless one at the beginning

(stars/(count*5)*5)

edited Jun 12 '17 at 13:52

answered Jun 12 '17 at 13:50

frankiehf

180
2
14

1

You cannot define a column alias and refer to it in the same query. – Phylogenesis Jun 12 '17 at 13:54

score 0 · Answer 2 · answered Jun 12 '17 at 13:54

Count is a reserved SQL term, if you want to use is as a field, you should do it like this:

select j.*,
    (select sum(stars) from ratingstar where jobid=j.id) as stars,
    (select count(*) from ratingstar where jobid=j.id) as count,
    ((stars/(`count`*5)*5)) as rating     //I have problem here if I remove this it works fine 
from jobs j inner join proposals p on p.jobid=j.id 
            inner join us_signup u on u.id=p.userid 
            inner join hired h on h.proposalid=p.id 
            where h.status="finished"

You need to quote it everywhere it's used using backtick quotes. — Sloan Thrasher, Jun 12 '17 at 13:58

score 0 · Answer 3 · answered Jun 12 '17 at 14:02

0

Since count is a reserved word/function, you need to quote it using backticks:

select j.*,
    (select sum(`stars`) from `ratingstar` where `jobid` = j.`id`) as `stars`,
    (select count(*) from `ratingstar` where `jobid` = j.`id`) as `count`,
    (`stars` / (`count` * 5) * 5) as `rating`
from `jobs` j 
inner join `proposals` p 
    on p.`jobid` = j.`id` 
inner join `us_signup` u 
    on u.`id` = p.`userid` 
inner join `hired` h 
    on h.`proposalid` = p.`id` 
where h.`status` = "finished"

answered Jun 12 '17 at 14:02

Sloan Thrasher

4,953
3
22
40

throws error #1054 - Unknown column 'stars' in 'field list' – Sugumar Venkatesan Jun 13 '17 at 05:26
Where does it come from in your query? if it's not in the ratingstar table, what are you counting? – Sloan Thrasher Jun 13 '17 at 05:51

mysql : calculations on alias fields in mysql

3 Answers3