Re: Please help on SQL

On 30 Mar 1998, Matthias Gresz wrote:

:)On Fri, 27 Mar 1998 21:03:46 GMT, john_at_demon.co.uk (John K) wrote:
:)
:)>In article <351A343B.5933F2EC_at_mns.bt.co.uk>, Ahmed <ahmed.dahdouh_at_mns.bt.co.uk>
:)>wrote:
:)>> Try
:)>> SELECT a, b
:)>> FROM foo
:)>> where 1 < (select count(*) from foo)
:)>> GROUP BY a, b;
:)>>
:)>> Nuno Guerreiro wrote:
:)>>
:)>> > On 25 Mar 1998 20:24:03 GMT, "Shetal Sheth"
:)>> > <sheths_at_bobcat.ent.ohiou.edu> wrote:
:)>> >
:)>> > SELECT a, b
:)>> > FROM foo
:)>> > GROUP BY a, b
:)>> > HAVING count(*) > 1;
:)>>
:)>>
:)>>
:)>
:)>Not sure that this will work. You can't group by unless either a or b is a group
:)>function which they are not. If there was a group function around a or b
:)>eg max(a) then you could group. Are you sure you don't want to order by ..
:)>
:)>John K
:)>
:)You can group by a and b, but since there's no grouping function in the select statement you'll be shown all touples <a,b>
:)appearing more than once..

Actually grouping by without using group functions is perfectly legitimate, depending on what question you want to answer.

Suppose you want to get a list of all the departments which have more than one employee for a certain job type.

SQL> select deptno, job
  2 from emp
  3 group by deptno, job having count(*) > 1;

    DEPTNO JOB
---------- ---------

        20 ANALYST
        20 CLERK
        30 SALESMAN