Proper use of extract function? [closed]

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 1

1. You have a missing comma after the alias "Total" in your first query.
2. 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>