CODEWITHSUNDEEP
mysqlsubquery
Sekdahai
Sekdahai

Reputation: 13

Mysql Aggregate operation subquery

$query = "Select first-second as ss from 
          (
           SELECT sum(mysum) from 
              (
                 SELECT pow(sum(ans),2)/count(staff_id) as mysum 
                 from answer 
                 group by staff_id
              )as EEx2
          ) as first from
          (
                  select pow(sum(ans),2)/count(ans) as sumofans
                  from answer 
          ) as second";

Error that i get : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from ( select pow(sum(ans),2)/count(ans) as sumofans from answer

I stucked with this for days.Can anyone help?

Upvotes: 0

Views: 53

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

This is your query:

Select first-second as ss from 
(
   SELECT sum(mysum) from 
   (
     SELECT pow(sum(ans),2)/count(staff_id) as mysum 
     from answer 
     group by staff_id
   ) as EEx2
) as first from 
(
  select pow(sum(ans),2)/count(ans) as sumofans
  from answer 
) as second

This is syntactically incorrect. A single select only has one from clause. Also, you don't name the column in the first query. I think you want:

Select (first.mysum - second.sumofans) as ss from 
(
  SELECT sum(mysum) as mysum from 
  (
   SELECT pow(sum(ans),2)/count(staff_id) as mysum 
   from answer 
   group by staff_id
   ) as EEx2
 ) as first cross join
 (
   select pow(sum(ans),2)/count(ans) as sumofans
   from answer 
 ) as second

Upvotes: 2

CodeSlayer
CodeSlayer

Reputation: 1327

$query = "Select first-second as ss from 
      (
       SELECT sum(mysum) from 
          (
             SELECT pow(sum(ans),2)/count(staff_id) as mysum 
             from answer 
             group by staff_id
          )as EEx2
      ) as first // use inner join , join etc here not from.. 
      (
              select pow(sum(ans),2)/count(ans) as sumofans
              from answer 
      ) as second";

The problem here is your using from key twice in single select.

Upvotes: 0

Related Questions