aggregate function on the from clause

Question

A query which should give output which has years as the column name and number of employee who hired in that year. Hire date will be their in column hire_date. I wrote a code its like SELECT count(e.employee_id) total, count(a.employee_id) _2002, count(b.employee_id) _2003, count(c.employee_id) _2004, count(d.employee_id) _2005 FROM hr.employees e, (select employee_id from hr.employees where extract(year from hire_date)=2002) a, (select employee_id from hr.employees where extract(year from hire_date)=2003) b, (select employee_id from hr.employees where extract(year from hire_date)=2004) c, (select employee_id from hr.employees where extract(year from hire_date)=2005) d;.
Its showing '0' in every column. Where i went wrong?

Note: data are present for all the conditions.

score 1 · Answer 1 · answered Dec 22 '15 at 08:21

I got the result using a slit different method SELECT count(employee_id) total, t1.a _2002, t2.b _2003, t3.c _2004, t4.d _2005 FROM hr.employees, (select count(employee_id) as a from hr.employees where extract(year from hire_date)=2002) t1, (select count(employee_id) as b from hr.employees where extract(year from hire_date)=2003) t2, (select count(employee_id) as c from hr.employees where extract(year from hire_date)=2004)t3, (select count(employee_id) as d from hr.employees where extract(year from hire_date)=2005) t4 group by t1.a , t2.b ,t3.c ,t4.d; It gives the same answer. but I want to know why the the query in the question don't work. Please do give me the reason. Thank you.

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

From observing your code, you are using multiple tables in the FROM clause with comma as a delimiter which is an IMPLICIT CROSS JOIN which returns the Cartesian product of rows from tables in the join. Its result would be the rows from Table A multiplied by rows from Table B with a 1:1 match. If you have 10 rows for each table and has a 1:1 match then the result would be 100 rows(in short, it returns the all the rows from both tables that are matched).In your case, your table listings doesnt have matched rows due to the WHERE clause/condition that makes their row unique so the result would obviously equal to 0.

Alternatively, you just use CASE STATEMENT and aggregate function SUM to get your expected result without using multiple table or join.Like this:

SELECT count(employee_id) total, 
            SUM(CASE WHEN EXTRACT(YEAR FROM hire_date)=2002 THEN 1 ELSE 0 END ) col_1999,
            SUM(CASE WHEN EXTRACT(YEAR FROM hire_date)=2003 THEN 1 ELSE 0 END ) col_1998, 
            SUM(CASE WHEN EXTRACT(YEAR FROM hire_date)=2004 THEN 1 ELSE 0 END ) col_1997, 
            SUM(CASE WHEN EXTRACT(YEAR FROM hire_date)=2005 THEN 1 ELSE 0 END ) col_1996 
            FROM hr.employees ;

Note that using commas to delimit multiple tables(IMPLICT JOIN) in a query is still working on ORACLE but it is preferred to use EXPLICIT JOIN. Read this to know why it is better to use EXPLICIT than IMPLICIT JOIN

aggregate function on the from clause

2 Answers2