Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Distinct not working when convert DATE using TO_CHAR

Re: Distinct not working when convert DATE using TO_CHAR

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 20 Dec 2005 11:12:54 -0500
Message-ID: <SeGdndchKfoXsDXenZ2dnUVZ_t6dnZ2d@comcast.com>

"SunScreen" <alampro_at_yahoo.com> wrote in message news:1135094215.307112.3980_at_o13g2000cwo.googlegroups.com...
>I think I m getting it. So I should revisit my question. How one can
> create a DISTINCT query per field? Is this possible?
>

look into GROUP BY

typical example:

SQL> select
  2 deptno

  3  , count(*) as ttl
  4  , sum(sal) as ttl_sal
  5  , round(avg(sal),2) as avg_sal
  6  , min(hiredate) as min_hire
  7  , max(hiredate) as max_hire

  8 from emp
  9 group by
 10 deptno
 11 /

    DEPTNO TTL TTL_SAL AVG_SAL MIN_HIRE MAX_HIRE

---------- ---------- ---------- ---------- --------- ---------
        10          3    8925.01       2975 09-JUN-81 13-SEP-05
        20          7   17262.18    2877.03 17-DEC-80 23-MAY-87
        30          5       9330       1866 20-FEB-81 28-SEP-81
        40          1

note though that the non-group-by expressions are all aggregate expressions -- you cannot mix single-row expressions and aggregate:

SQL> select
  2 deptno

  3  , job
  4  , count(*) as ttl
  5  , sum(sal) as ttl_sal
  6  , round(avg(sal),2) as avg_sal
  7  , min(hiredate) as min_hire
  8  , max(hiredate) as max_hire

  9 from emp
 10 group by
 11 deptno
 12 /
, job
  *
ERROR at line 3:
ORA-00979: not a GROUP BY expression

SQL> select
  2 deptno

  3  , job
  4  , count(*) as ttl
  5  , sum(sal) as ttl_sal
  6  , round(avg(sal),2) as avg_sal
  7  , min(hiredate) as min_hire
  8  , max(hiredate) as max_hire

  9 from emp
 10 group by
 11 deptno, job
 12 /

    DEPTNO JOB TTL TTL_SAL AVG_SAL MIN_HIRE MAX_HIRE

---------- --------- ---------- ---------- ---------- --------- ---------
        10 Pest               1        200        200 13-SEP-05 13-SEP-05
        10 CLERK              1       3725       3725 09-JUN-81 09-JUN-81
        10 PRESIDENT          1    5000.01    5000.01 17-NOV-81 17-NOV-81
        20 ADMIN              1       6000       6000 23-MAY-87 23-MAY-87
        20 CLERK              1    3051.81    3051.81 02-APR-81 02-APR-81
        20 WIZARD             1
        20 ANALYST            1     3060.3     3060.3 03-DEC-81 03-DEC-81
        20 MANAGER            1       2000       2000 17-DEC-80 17-DEC-80
        20 TRAINER            1     3060.3     3060.3 19-APR-87 19-APR-87
        20 ENGINEER           1      89.77      89.77 01-JAN-82 01-JAN-82
        30 REP                3       4980       1660 20-FEB-81 28-SEP-81
        30 ADMIN              1       2850       2850 01-MAY-81 01-MAY-81
        30 SALESMAN           1       1500       1500 08-SEP-81 08-SEP-81
        40 SCIENTIST          1

++ mcs Received on Tue Dec 20 2005 - 10:12:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US