Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Distinct not working when convert DATE using TO_CHAR
"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
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
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
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