How to display following matrix?

orlando · #1 (**permalink**) 03-07-2007

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

Innila · #2 (**permalink**) 03-08-2007

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

If you want to know more about Group by, i suggest you to go through the following link,
http://www.techonthenet.com/sql/group_by.php

Guess i've got your problem right.
Hopefully the SQL query & the link helps you.

orlando · #3 (**permalink**) 03-08-2007

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

bbkcdb · #4 (**permalink**) 03-12-2007

select AVG(tax) from invoice where type='sales tax'
what is the error n above query

orlando · #5 (**permalink**) 03-13-2007

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Display n-1 rows from a table	dbtester	SQL Server	3	02-07-2008 07:11 AM
Display data in datagrid	JobHelper	VB.NET	2	06-05-2007 09:10 AM
Display record ordered by the day of the week	orlando	SQL	3	03-01-2007 02:04 AM
Need predefined Function or code for Data Display in PHP	norman	PHP	0	07-17-2006 04:17 PM

How to display following matrix?

How to display following matrix?

Similar Threads