Question :
I am trying to select names of employees who were born in 1985.
I am doing this but it is giving me an error.
SQL> select fname, lname, count(E#) as "total"
2 extract(year from date, dob) as "year"
3 from employee
4 where "year"=1985;
extract(year from date, dob) as "year"
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> select fname, lname, count(E#) as "total"
2 from employee
3 where extract(year from date, dob) =1985
4 group by fname, lname;
where extract(year from date, dob) =1985
*
ERROR at line 3:
ORA-00936: missing expression
Answer :
- You have a missing comma after the alias “Total” in your first query.
- Extract will take single column as input, while you are giving two columns.
You could do it like:
SQL> SELECT COUNT(deptno) AS "total",
2 extract(YEAR FROM hiredate) AS "year"
3 FROM emp
4 GROUP BY extract(YEAR FROM hiredate);
total year
---------- ----------
2 1982
1 1983
1 1980
10 1981
SQL>
UPDATE
Using the WHERE clause:
SQL> SELECT COUNT(deptno) AS "total",
2 extract(YEAR FROM hiredate) AS "year"
3 FROM emp
4 WHERE EXTRACT(YEAR FROM hiredate) = 1981
5 GROUP BY extract(YEAR FROM hiredate);
total year
---------- ----------
10 1981
SQL>