Home » SQL & PL/SQL » SQL & PL/SQL » Matrix Type Results from Query (I could use some help)
Matrix Type Results from Query (I could use some help) [message #194229] Thu, 21 September 2006 04:24 Go to next message
kathyjomoore
Messages: 4
Registered: September 2006
Location: USA
Junior Member
Problem: I need to create a query to display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998.
The result of the query should produce a matrix like this:

<http://scatcat.fhsu.edu/~kjmoore2/p4_10.gif> Graphic of required results

My query:
SELECT  *
FROM (SELECT COUNT(employee_id) "Total",
sum(decode(hire_date,'%95%',1,0)) "1995",
sum(decode(hire_date,'%96%',1,0)) "1996",
sum(decode(hire_date,'%97%',1,0)) "1997",
sum(decode(hire_date,'%98%',1,0)) "1998"
FROM employees
GROUP BY hire_date);

RESULT: Layout of matrix is correct, but data is crazy.
<http://scatcat.fhsu.edu/~kjmoore2/P4_10matrix_crazy_data.gif"> Graphic of my results.

<http://scatcat.fhsu.edu/~kjmoore2/EmployeeTBL.gif"> Graphic of the employee tabel.

Sorry I don’t know how to: “Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)” as Maaher suggests in topic How to get a quick answer to your question: TIPS AND TRICKS

This is an assignment for a course I am taking, but we are allowed to use the Internet as a resource. Any help on how I can correct this would be greatly appreciated.

Thank you.
Kathy
Re: Matrix Type Results from Query (I could use some help) [message #194235 is a reply to message #194229] Thu, 21 September 2006 04:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When they said
Quote:

but we are allowed to use the Internet as a resource
, I've got a sneaking suspicion that they were thinking more along the lines of you finding out information and working out how to solve the problem rather than asking someone else to do it over the internet.

Your basic query is sound, but you've got into a bit of a muddle over the hire_date column, and how string comparisons work. Dates in oracle are stored in an internal date format (it's basically a julian date stored in a number, but let's not worry about that at the moment). If you want to get part of the date out of a DATE field, it's really important that you use the TO_CHAR command, and specify an explicit date format. If you don't, then Oracle will just go ahead and do an implicit to_char and use whatever default date format it runs across.

The % operator only works as a wildcard when you use the LIKE command, and you can't use that inside a DECODE.

You just need to change:
sum(decode(hire_date,'%95%',1,0))
to
sum(decode(to_char(hire_date,'yyyy'),'1995',1,0))
to make that bit work.

Now, as to the number of rows you're getting in the results.
The 'GROUP BY hire_date' will cause the query to return one row per distinct value of hire_date, whereas you want only one row for the query.
Solution: Remove the "SELECT * FROM (" and the "GROUP BY hire_date;" and you should be there.
Re: Matrix Type Results from Query (I could use some help) [message #194239 is a reply to message #194229] Thu, 21 September 2006 04:56 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This is One option :

SQL> SELECT TO_CHAR(HIREDATE,'yyyy') yy , COUNT(*) cnt
  2  FROM emp
  3  GROUP BY TO_CHAR(HIREDATE,'yyyy');

YY          CNT
---- ----------
1980          1
1981         11
1982          1
1987          2
1992          1

SQL> SELECT MAX(DECODE(yy,'1980',cnt)) "1980",
  2        MAX(DECODE(yy,'1981',cnt)) "1981",
  3        SUM(cnt) total
  4         FROM
  5  (SELECT TO_CHAR(HIREDATE,'yyyy') yy , COUNT(*) cnt
  6  FROM emp
  7  GROUP BY TO_CHAR(HIREDATE,'yyyy'));

      1980       1981      TOTAL
---------- ---------- ----------
         1         11         16

SQL>


Thumbs Up
Rajuvan
Re: Matrix Type Results from Query (I could use some help) [message #194249 is a reply to message #194229] Thu, 21 September 2006 05:20 Go to previous message
kathyjomoore
Messages: 4
Registered: September 2006
Location: USA
Junior Member
Hi,

Thank you.

Quote:

, I've got a sneaking suspicion that they were thinking more along the lines of you finding out information and working out how to solve the problem rather than asking someone else to do it over the internet.


Had you not explained what I was doing wrong and just wrote the query then I would be wrong to use your answer.

I will go back and review:
GROUP BY clause
TO_CHAR function with dates

Because I missed something in that section, or did not understand it in the first palce.

Thanks again for pointing out where I was off-track.

The query that worked:
SELECT COUNT(employee_id) "Total",
SUM(decode(to_char(hire_date,'yyyy'),'1995',1,0)) "1995",
SUM(decode(to_char(hire_date,'yyyy'),'1996',1,0)) "1996",
SUM(decode(to_char(hire_date,'yyyy'),'1997',1,0)) "1997",
SUM(decode(to_char(hire_date,'yyyy'),'1998',1,0)) "1998"
FROM employees;
Previous Topic: package spec/body
Next Topic: GROUP BY
Goto Forum:
  


Current Time: Sat Dec 03 01:26:12 CST 2016

Total time taken to generate the page: 0.08840 seconds