You are viewing a single comment's thread. Return to all comments →
I don't think GROUP BY <alias> works for Oracle. Can someone verify?
GROUP BY <alias>
I can confirm that GROUP BY clearly seems to work for Oracle, altho remember that the table ALIAS works differently there as they forbid the 'AS' keyword for table names (it is ok with col names):
SELECT MAX_EARNINGS, COUNT(EMPLOYEE_ID) FROM (SELECT MAX(EARNINGS) AS MAX_EARNINGS FROM (SELECT MONTHS * SALARY AS EARNINGS FROM EMPLOYEE) EARNINGS_TABLE) MAX_EARNINGS_TABLE, EMPLOYEE WHERE MONTHS * SALARY = MAX_EARNINGS GROUP BY MAX_EARNINGS ;
Without indentation (HR wants us to enter these things as one long line!) I consider these completely write-only, who can make sense of that without indentation?? But, it works.
we can reduce number of sub queries here.
my version for above query :
select (salarymonths)earning,count() from employee where (salarymonths)=(select max(salarymonths) earning from employee) group by (salarymonths);