SELECT with group function

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

Poston Thu, 16 Feb 2006 3:29 pm

Guys, good afternoon I need do a select that bring me the following table.
In this query, provide a date that specifies to dt_criacao. So TOT_DIA will be the number of accounts created on dt_criacao, TOT_MES will be the number of accounts created within the month of dt_criacao and hence TOT_ANO will be the number of accounts created within the year which is provided in dt_criacao, IE: dt_criacao = 20051206, shortly month = 12 year = 2005
Code: Select all
PRODUTO      TOT_DIA        TOT_MES       TOT_ANO
   4           125            500           1200
   5             0            300            950
   6            50             50             50
For this I use a single table named accounts with the dt_criacao and product fields.

For the TOT_DIA can do the select, but to bring the TOT_MES and TOT_ANO, respectively for product I haven't been able to do. could someone give me a help?!!

To bring the two first columns the select is:
Code: Select all
select produto, count(dt_criacao)
from contas
where dt_criacao = "20051206"
group by produto
could someone give me a help?!!

Bye and thank you!!
Jedi
Location: São Paulo

Jedi

Poston Thu, 16 Feb 2006 4:05 pm

I did an example with SCOTT. Take a look:
Code: Select all
SQL> select deptno, hiredate
  2  from emp
  3  where to_char(hiredate, 'yyyy') = 1981
  4  order by 1,2
  5  /

    DEPTNO HIREDATE
---------- ---------
        10 09-JUN-81
        10 17-NOV-81
        20 02-APR-81
        20 03-DEC-81
        30 20-FEB-81
        30 22-FEB-81
        30 01-MAY-81
        30 08-SEP-81
        30 28-SEP-81
        30 03-DEC-81

10 rows selected.
Now, let's add up the way you need:
Code: Select all
SQL> select a.dep, b.qtd_dia, c.qtd_mes, d.qtd_ano
  2  from
  3    (select distinct deptno dep
  4     from emp
  5    ) a
  6  , (select deptno, count(1) qtd_dia
  7     from emp
  8     where to_char(hiredate,'dd/mm/yyyy')  = '20/02/1981' group by deptno)   b
  9  , (select deptno, count(1) qtd_mes
10     from emp
11     where to_char(hiredate,'mm/yyyy')     = '02/1981'    group by deptno)   c
12  , (select deptno, count(1) qtd_ano
13     from emp
14     where to_char(hiredate,'yyyy')        = '1981'       group by deptno)   d
15  where a.dep=b.deptno(+)
16    and a.dep=c.deptno(+)
17    and a.dep=d.deptno(+)
18  /

       DEP    QTD_DIA    QTD_MES    QTD_ANO
---------- ---------- ---------- ----------
        10                                2
        20                                2
        30          1          2          6

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



Return to SQL

Who is online

Users browsing this forum: Google [Bot] and 5 guests