MySQL Select An cu cele mai multe randuri inregistrate

Discutii despre script-uri si coduri PHP-MySQL, precum si lucru cu XML in PHP.
patricia
Mesaje:82

MySQL Select An cu cele mai multe randuri inregistrate

Am incercat urmatoarea interogare in mysql versiunea 6:

Cod: Selectaţi tot

SELECT YEAR(HIREDATE) FROM EMP WHERE YEAR(HIREDATE)=(SELECT MAX(COUNT(EMPNO))
                            FROM EMP
                            GROUP BY YEAR(HIREDATE));
La problema cu acest enunt:
- In ce an sau angajat cei mai multi in companie? (coloana "hiredate" reprezinta data angajarii pentru fiecare angajat)

Apare eroarea: "Invalid use of group function".
Tabelul "EMP" este:

Cod: Selectaţi tot

EMPNO ENAME   JOB       MGR  HIREDATE     SAL      COMM DEPTNO
----- -----  -------   ----  -------- --------  ------- ------
7369 SMITH  CLERK     7902 13-JUN-83   800.00                       20
7499 ALLEN  SALESMAN  7698 15-AUG-83 1,600.00   300.00      30
7521 WARD   SALESMAN  7698 26-MAR-84 1,250.00   500.00     30
7566 JONES  MANAGER   7839 31-OCT-83 2,975.00                   20
7654 MARTIN SALESMAN  7698 05-DEC-83 1,250.00  1,400.00   30
7698 BLAKE  MANAGER   7839 11-JUL-84 2,850.00                    30
7782 CLARK  MANAGER   7839 14-MAY-84 2,450.00                  10
7788 SCOTT  ANALYST   7566 05-MAR-84 3,000.00                  20
7839 KING   PRESIDENT          09-JUL-84 5,000.00                   10
7844 TURNER SALESMAN  7698 04-JUN-84 1,500.00  .00          30
7876 ADAMS  CLERK     7788 04-JUN-84 1,100.00                   20
7900 JAMES  CLERK     7698 23-JUL-84   950.00                     30
7902 FORD   ANALYST   7566 05-DEC-83 3,000.00                 20
7934 MILLER CLERK     7782 21-NOV-83 1,300.00                 10
MarPlo Mesaje:4343
Buna
Nu ma prea pricep la functii si comenzi MySQL, dar din enuntul erorii se poate ca utilizarea acestei alcatuiri de functii: MAX(COUNT(EMPNO)) sa nu fie corecta.
Sau poate de la: GROUP BY YEAR(HIREDATE)
Poate ar trebui: "GROUP BY HIREDATE" , dar nu stiu sigur.

Incearca asa:

Cod: Selectaţi tot

SELECT YEAR(hiredate) AS an, count(*) AS total FROM emp
 GROUP BY YEAR(hiredate)
 ORDER BY COUNT(*) DESC LIMIT 1
Ori asa:

Cod: Selectaţi tot

SELECT year(hiredate) AS an, count(*) AS total FROM emp GROUP BY hiredate ORDER BY total DESC LIMIT 1;
Sau asta:

Cod: Selectaţi tot

SELECT year(hiredate) AS an, COUNT(*) AS total FROM emp GROUP BY YEAR(hiredate) ORDER BY total DESC LIMIT 1

Subiecte similare