Results 1 to 5 of 5

Thread: How to display following matrix?

  1. #1
    Junior Member
    Join Date
    Feb 2007
    Answers
    6

    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

    Last edited by orlando; 03-08-2007 at 06:30 PM.

  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    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
    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.

    Last edited by Innila; 03-08-2007 at 08:21 AM.
    *** Innila ***

  3. #3
    Junior Member
    Join Date
    Feb 2007
    Answers
    6

    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


  4. #4
    Junior Member
    Join Date
    Mar 2007
    Answers
    2

    Re: How to display following matrix?

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


  5. #5
    Junior Member
    Join Date
    Feb 2007
    Answers
    6

    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact