Re: sql quesry basic question

Comments embedded.
soalvajavab1_at_yahoo.com wrote:
> very basic question but how can I have emp_name in result set as well
> in the following scenario:
>
> select count(1), aa.dept_no , aa.emp_name
> from a_target3 aa, a_src2_lookup bb
> where aa.dept_no = bb.dept_no
> group by aa.dept_no, aa.emp_name
>
> WRONG RESULT:
>
> COUNT(1) DEPT_NO EMP_NAME
> 1 10 Lisa Sharp
> 1 10 David Jr. Adams
> 1 11 John Travolta
> 1 11 Sara F. McDonald
> 1 12
> 1 20 Mike Sr Sovolfski

No, correct result, as you're grouping by TWO columns, not one. Simply because it isn't what you EXPECT doesn't make it WRONG.

>
>
> NOW without emp_name:
>
> select count(1), aa.dept_no
> from a_target3 aa, a_src2_lookup bb
> where aa.dept_no = bb.dept_no
> group by aa.dept_no
>
>
> COUNT(1) DEPT_NO
> 2 10
> 2 11
> 1 12
> 1 20
>
> CORRECT RESULT
Again, BOTH results are CORRECT, it's that the latter result is what you EXPECT. See the explanation above. You might think of using analytic functions to do this. Using the SCOTT schema to illustrate the point:

SQL> select count(empno) over (partition by deptno) emp_ct, ename, deptno
  2 from emp;

    EMP_CT ENAME DEPTNO
---------- ---------- ----------

         3 CLARK              10
         3 KING               10
         3 MILLER             10
         5 JONES              20
         5 FORD               20
         5 ADAMS              20
         5 SMITH              20
         5 SCOTT              20
         6 WARD               30
         6 TURNER             30
         6 ALLEN              30
         6 JAMES              30
         6 BLAKE              30
         6 MARTIN             30

14 rows selected.

>
> Please advise

David Fitzjarrell Received on Fri Oct 06 2006 - 15:11:14 CDT