Range of dates

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 24 Apr 2006 9:33 am

Guys, I have a table that has the following fields: (DATE), END (DATE), ID (NUMBER).

Assuming I have the following registry: START: 2/1/1990 END: 2/1/1995 ID: 2 How would I do a select count to return the years 1990 to 1995? Is there any way?

Thank you
marcoantoniorocha
Location: São Carlos - SP

Marco Antonio Rocha

Poston Mon, 24 Apr 2006 9:55 am

Do it like this:
Code: Select all
SELECT COUNT(ID)
FROM TABELA
WHERE INICIO >= TO_DATE('1990', 'YYYY')
  AND FIM    <= TO_DATE('1995', 'YYYY')
of course, this sql will only work if the BEGINNING is actually smaller than the END.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Mon, 24 Apr 2006 10:23 am

Thanks for the reply, but I guess I didn't express myself very well. :)

I would like a count per year. In my example, I need 6 rows are returned: year: 1990-COUNT: 1 year: 1991-COUNT: 1 year: 1992-COUNT: 1 year: 1993-COUNT: 1 year: 1994-COUNT: 1 year: 1995-COUNT: 1 Not found yet any way to do this. Is it possible?
marcoantoniorocha
Location: São Carlos - SP

Marco Antonio Rocha

Poston Mon, 24 Apr 2006 11:10 am

I'm going to introduce you to the GROUP BY clause of the SQL statement.
See the example below with the SCOTT
Code: Select all
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE     
---------- ---------- --------- ---------- ---------
      7369 SMITH      CLERK           7902 17-DEC-80
      7499 ALLEN      SALESMAN        7698 20-FEB-81
      7521 WARD       SALESMAN        7698 22-FEB-81
      7566 JONES      MANAGER         7839 02-APR-81
      7654 MARTIN     SALESMAN        7698 28-SEP-81
      7698 BLAKE      MANAGER         7839 01-MAY-81
      7782 CLARK      MANAGER         7839 09-JUN-81
      7788 SCOTT      ANALYST         7566 19-APR-87
      7839 KING       PRESIDENT            17-NOV-81
      7844 TURNER     SALESMAN        7698 08-SEP-81
      7876 ADAMS      CLERK           7788 23-MAY-87
      7900 JAMES      CLERK           7698 03-DEC-81
      7902 FORD       ANALYST         7566 03-DEC-81
      7934 MILLER     CLERK           7782 23-JAN-82

14 rows selected.

SQL> SELECT TO_CHAR(HIREDATE, 'YYYY'), COUNT(EMPNO) QTD
  2  FROM EMP
  3  GROUP BY TO_CHAR(HIREDATE, 'YYYY')
  4  /

TO_C        QTD
---- ------------
1980            1
1981           10
1982            1
1987            2

SQL>
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Mon, 24 Apr 2006 11:20 am

That's what I'm doing. But it still does not completely solve my problem.

Using your example (think it's easier to explain). If I want to know how many employees were hired by year in the period from 1980 to 1990, how am I supposed to do to get a result like this:
Code: Select all
TO_C        QTD
---- ------------
1980            1
1981           10
1982            1
1983            0
1984            0
1985            0
1986            0
1987            2
1988            0
1989            0
1990            0
marcoantoniorocha
Location: São Carlos - SP

Marco Antonio Rocha

Poston Tue, 02 May 2006 1:17 pm

Marcos, try this query!

Code: Select all
select      ano,
              count(id)
from       empregados
group by ano
having    count(id) >= 0
order by ano
regards, Álvaro
asarmento
Location: Salvador - BA

Álvaro Sarmento
Analista de Sistemas
Unitech - Tecnologia de Informação
www.unitech.com.br



Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests