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" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
