i am trying to use the count function to count the number of people that where hired in different years, but I am having trouble getting it to seperate the different items that I need. I need for it to tell me how many employees that I have in one column and then in the next couple of columns tell me how many were hired in a particular year. I can get one or the other to work, but when I filter the results it also filter the total number of employees to. I am using Oracle9 I believe if that helps.

you'll have to do several different inquiries and I have no idea how to write Oracle as I do PHP only. here's the SQL syntax though from what I remember

for a total count:

SELECT COUNT(column_name) FROM database_name;


for each year, I need to know how the date hired field is setup (i.e. is it YYYYMMDD or 20090101 for Jan 1, 2009?).

Basically you'll just do something like this for the setup above:

SELECT COUNT(column_name) FROM database_name WHERE column_name = 2009*;

This will count every item in the column with a 2009 hired date. for january, you'd use "200901*" to select all the dates in january, 2009. to select a range you'd enter "20090109 >= column_name >= 20090101" for all dates between january 1 and january 9, 2009 including those dates.


In PHP, I'd setup variables like $totalCount, $janCount, $febCount and so on and place them in a table that displays on HTML. I have no idea how to run multiple queries and display it using just Oracle.

That is the exact way that I am trying to attach the problem, but I can't seem to figure out how to set up different columns that have essentially the same information in it. I thought about trying to set up aliases and that works to a point, but when I go down and do my where statement to filter out say all of the none 2009 entries it filters out that info out of all of the columns and not just the one.

What exactly are you trying to do? A specific example would be useful.
What's the table structure that you're trying to query and what's the output you want out of it? Select count(*) from table where datediff(y, <<your date column>>, 2009) = 0 might work, but I need more info...

Hello.

Looks like you want to use column aliases and sub-queries.

Post your table structure and I'll whip you up a query.

Ta,

Chris.

SQL> describe emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NOT NULL NUMBER(2)

SQL> select *
2 from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

7782 CLARK MANAGER 7839 09-JUN-81 2450
10


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7900 JAMES CLERK 7698 03-DEC-81 950
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20

7369 SMITH CLERK 7902 17-DEC-80 800
20

7788 SCOTT ANALYST 7566 09-DEC-82 3000
20


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 12-JAN-83 1100
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10


14 rows selected.

Here is what I have been able to come up with so far, but it isn't there yet. I need for the columns to actually list the total number hired in a specific year, but it breaks down the numbers in rows instead.

SQL> select count(total.hiredate), count(worker.hiredate)
from emp total, emp worker
where worker.hiredate>'01-MAY-81'
and total.hiredate=worker.hiredate;

COUNT(TOTAL.HIREDATE) COUNT(WORKER.HIREDATE)
--------------------- ----------------------
11 11

SQL> select count(total.hiredate), count(worker.hiredate)
2 from emp total, emp worker
3 where worker.hiredate>'01-MAY-81'
4 and total.hiredate=worker.hiredate
5 group by total.hiredate, worker.hiredate;

COUNT(TOTAL.HIREDATE) COUNT(WORKER.HIREDATE)
--------------------- ----------------------
1 1
1 1
1 1
1 1
4 4
1 1
1 1
1 1

8 rows selected.

I finally figured it out. I had to use the DECODE keyword and then add up the boolean results. Thanks to all that tried to assist me.

I do not have the answer to your question, but if you have time, please consider this:

Microsoft and the rest of the software industry have forced people to learn their software without learning the code itself.

It is easier, faster, and better designed if you code it youself (and if that is something you do on a regular basis). Do not be a slave to the latest programmer's whim to move a button or menu bar to a place no one can find it.

Are you suggesting he writes a complete RDBMS himself?

I believe that I was trying to find the right way to do it first and then the most efficient way. I agree that more people should learn the code for the back end of things, but we all have to start somewhere.