[SOLVED] Is there a way to use values obtained in a query, in that same query?

[morphboy23]

Here's what I have:

"SELECT 

(SELECT COUNT(*) 
     FROM inquiries 
     WHERE iduser = '$id') AS icount, 
(SELECT COUNT(*) 
     FROM responses 
     WHERE iduser = '$id') AS rcount, 
(SELECT IFNULL( SUM( total_value ) / SUM( total_votes ) , 0 ) / 5 
     FROM ratings, responses 
     WHERE responses.iduser = '$id' 
          AND responses.idresponse = ratings.id 
          AND ratings.total_votes > 0) AS avgscore, 
FLOOR(10 * icount + rcount * (20 + 40 * avgscore)) AS score 

FROM users LIMIT 1"

 

It says it doesn't recognize 'icount,' 'rcount' or 'avgscore' in the FLOOR statement. The reason I'm doing this is because I want all 3 values that make up the score, as well as the score itself, returned.

[Illusion]

why don't u try the alias name with in the select statement like

 

count(*) as icount

 

count(*) as rcount

 

(IFNULL( SUM(ratings.total_value ) / SUM( responses.total_votes ) , 0 ) )/ 5  as average

 

[morphboy23]

Hm, I figured it out. I had to put the FLOOR statement in its own SELECT. I have no idea why that would fix it, but hey..