How to display following matrix?
Need to create a query to display the total number of employees and the number of employees hired in 1995, 1996, 1997 and 1998. i have difficulty of creating following format. not sure how to use group by clause
Total | 1995 | 1996 | 1997 | 1998
20 | 1 | 2 | 2 | 3
Thank you,
Orlando
Re: How to display following matrix?
Hi Orlando,
Group by clause will help you retrieving the query for total number of employees hired in each year.
Formatting the Query results will give you the overall total number of employees.
The below code will satisfy this,
[Code]
break on report skip 2
compute sum label 'Total' of "NO OF EMP" on report
select ' ', to_char(hiredate,'yyyy') "YEAR", count(*) "NO OF EMP" from emp
group by to_char(hiredate,'yyyy')
/
'' YEAR NO OF EMP
----------- ---- ----------
1980 1
1981 10
1982 1
1987 2
1999 1
----------
TOTAL 19
[/Code]
If you want to know more about Group by, i suggest you to go through the following link,
[url]http://www.techonthenet.com/sql/group_by.php[/url]
Guess i've got your problem right.
Hopefully the SQL query & the link helps you.
Re: How to display following matrix?
Hi, Innila,
Thank you for your answer.
Your result data are what they want, but the problem ask to display the Total and years as first row, and the numbers are on the second row. I can not figure out how to display this format?
Orlando
Re: How to display following matrix?
select AVG(tax) from invoice where type='sales tax'
what is the error n above query
Re: How to display following matrix?
Hi,
Here is my solution. I would like to share it.
select count(*) as Total,
count ( case when to_char(hire_date,'YYYY') = '1995' then hire_date else NULL end) as "1995",
count ( case when to_char(hire_date,'YYYY') = '1996' then hire_date else NULL end) as "1996",
count ( case when to_char(hire_date,'YYYY') = '1997' then hire_date else NULL end) as "1997",
count ( case when to_char(hire_date,'YYYY') = '1998' then hire_date else NULL end) as "1998"
from employees
June