List:General Discussion« Previous MessageNext Message »
From:ViSolve DB Team Date:December 27 2006 7:14am
Subject:Re: oracle transfer question
View as plain text  
Hi,
Try using STDDEV(), which was given for Oracle compatibility by MySQL.

SELECT employee_id, salary, hire_date, STDDEV(salary) "Std Deviation of 
Salary" FROM employees WHERE job_id = 'ST_CLERK' Group by employee_id, 
salary, hire_date ORDER BY hire_date;

Thanks,
ViSolve DB Team.
----- Original Message ----- 
From: "wangxu" <wangxu@stripped>
To: <mysql@stripped>
Sent: Thursday, December 21, 2006 8:29 AM
Subject: oracle transfer question


> There is a oracle SQL:
> SELECT employee_id, salary, hire_date, STDDEV(salary)
> OVER (ORDER BY hire_date) "Std Deviation of Salary"
> FROM employees WHERE job_id = 'ST_CLERK';
>
> The result is:
> EMPLOYEE_ID     SALARY HIRE_DATE  Std Deviation of Salary
> ----------- ---------- ---------- -----------------------
>        137       3600 14-7月 -95                       0
>        141       3500 17-10月-95              70.7106781
>        133       3300 14-6月 -96              152.752523
>        142       3100 29-1月 -97              221.735578
>        131       2500 16-2月 -97              435.889894
>        125       3200 16-7月 -97              389.871774
>        129       3300 20-8月 -97              357.903951
>        138       3200 26-10月-97              331.393163
>        130       2800 30-10月-97              339.116499
>        139       2700 12-2月 -98              352.136337
>        143       2600 15-3月 -98              369.028208
>
> EMPLOYEE_ID     SALARY HIRE_DATE  Std Deviation of Salary
> ----------- ---------- ---------- -----------------------
>        140       2500 06-4月 -98              388.762606
>        144       2500 09-7月 -98              399.679359
>        134       2900 26-8月 -98              384.664832
>        126       2700 28-9月 -98              377.586319
>        127       2400 14-1月 -99               390.72582
>        132       2100 10-4月 -99              427.974023
>        135       2400 12-12月-99              430.116263
>        136       2200 06-2月 -00              443.800703
>        128       2200 08-3月 -00              453.379126
>
> I transfer the oracle sql to mysql sql:
>
> SELECT employee_id, salary, hire_date, STDDEV_pop(salary)"Std Deviation of 
> Salary" FROM employees WHERE job_id = 'ST_CLERK' Group by employee_id, 
> salary, hire_date ORDER BY hire_date;
>
> But the result are:
> +-------------+--------+------------+-------------------------+
> | employee_id | salary | hire_date  | Std Deviation of Salary |
> +-------------+--------+------------+-------------------------+
> | 137         | 3600   | 1995-07-14 | 0.0000                  |
> | 141         | 3500   | 1995-10-17 | 0.0000                  |
> | 133         | 3300   | 1996-06-14 | 0.0000                  |
> | 142         | 3100   | 1997-01-29 | 0.0000                  |
> | 131         | 2500   | 1997-02-16 | 0.0000                  |
> | 125         | 3200   | 1997-07-16 | 0.0000                  |
> | 129         | 3300   | 1997-08-20 | 0.0000                  |
> | 138         | 3200   | 1997-10-26 | 0.0000                  |
> | 130         | 2800   | 1997-10-30 | 0.0000                  |
> | 139         | 2700   | 1998-02-12 | 0.0000                  |
> | 143         | 2600   | 1998-03-15 | 0.0000                  |
> | 140         | 2500   | 1998-04-06 | 0.0000                  |
> | 144         | 2500   | 1998-07-09 | 0.0000                  |
> | 134         | 2900   | 1998-08-26 | 0.0000                  |
> | 126         | 2700   | 1998-09-28 | 0.0000                  |
> | 127         | 2400   | 1999-01-14 | 0.0000                  |
> | 132         | 2100   | 1999-04-10 | 0.0000                  |
> | 135         | 2400   | 1999-12-12 | 0.0000                  |
> | 136         | 2200   | 2000-02-06 | 0.0000                  |
> | 128         | 2200   | 2000-03-08 | 0.0000                  |
> +-------------+--------+------------+-------------------------+
>
> Why all the compute column values are 0?What is the correct sql?
> thanks!
>
>
>
> 

Thread
oracle transfer questionwangxu21 Dec
  • Re: oracle transfer questionViSolve DB Team27 Dec