how to display the output in oracle with the select query

Question

Below is my EMP table with some sample data

EMPNAME | WORK_DATA | WORK_HOURS
---------------------------------
abc     | 01-SEP-13 |     9
xyz     | 01-SEP-13 |     8
abc     | 02-SEP-13 |     8
xyz     | 02-SEP-13 |     7
abc     | 01-OCT-13 |     5
xyz     | 01-OCT-13 |     7
abc     | 02-OCT-13 |     8
xyz     | 02-OCT-13 |     7

And now I want this to be displayed as in oracle using select statement

EMPNAME | SEP_MONTH_WORKING_HOURS | OCT_MONTH_WORKING_HOURS 
-----------------------------------------------------------
abc     | 17                      | 13
xyz     | 15                      | 14

Seond doubt is:

EMPNAME | SEP_MONTH_WORKING_HOURS | RANK 
-----------------------------------------------------------
xyz     | 15                      | 1
abc     | 17                      | 2

Second question answer is : select empid,work_date OCT_MONTH_WORK_HOURS,rownum rank from (select empid,sum(case when extract (month from work_date) = 10 then work_hours end) work_date from temp group by empid order by work_date) — user2870912, Oct 11 '13 at 13:53

score 2 · Answer 1 · answered Oct 11 '13 at 12:31

2

This should work using extract:

select empname, 
  sum(case when  extract(month from work_data) = 9 then work_hours end) sept_work_hours,
  sum(case when  extract(month from work_data) = 10 then work_hours end) oct_work_hours
from emp
group by empname

SQL Fiddle Demo

Please note, abcs employee for september should be 17 instead of 19.

answered Oct 11 '13 at 12:31

sgeddes

62,311
6
61
83

Man your too good... this worked for me... thanks alot :) – user2870912 Oct 11 '13 at 12:43
@user2870912 please accept the answer if it worked for you. – krokodilko Nov 02 '13 at 11:39

how to display the output in oracle with the select query

1 Answers1