Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    Dec 2011
    Posts
    2

    Unanswered: Query Help

    Hi,

    I have a requirement to simplify an existing query.
    The existing query looks as shown below.

    SELECT
    1.COLA,
    1.COLB,
    2.COLA,
    2.COLB
    FROM
    (
    SELECT DISTINCT
    COLA,
    COLB
    FROM
    EMPLOYEE
    ) 1
    LEFT OUTER JOIN
    (
    SELECT
    SUM(COLA),
    SUM(COLB)
    FROM
    EMPLOYEE
    GROUP BY DEPTNO
    ) 2
    ON
    1.DEPTNO=2.DEPTNO

    Problem: The current query is doing 2 reads on the same table to accomplish the desired result.

    1st select is doing a distinct select on table A and the 2nd table is joined through left outer join where the aggregation is being done.

    I would like to accomplish the result through single read on the table. The sample data is given below where we are grouping on DEPT NO.



    Source Data:

    Employee id Name Dept No Salary
    1000 Q MECH $500.00
    1001 D ECE $1,000.00
    1002 R EEE $502.00
    1003 X MECH $600.00
    1004 T EEE $800.00
    1005 A ECE $200.00
    1006 V ECE $1,100.00
    1007 C EEE $300.00
    1008 Z Mech $400.00
    1009 B Mech $700.00
    1010 N EEE $150.00

    Result to be like:

    Employee id Name Dept No Sum (Salary)
    1000 Q MECH $2,200.00
    1001 D ECE $2,300.00
    1002 R EEE $1,752.00
    1003 X MECH $2,200.00
    1004 T EEE $1,752.00
    1005 A ECE $2,300.00
    1006 V ECE $2,300.00
    1007 C EEE $1,752.00
    1008 Z MECH $2,200.00
    1009 B MECH $2,200.00
    1010 N EEE $1,752.00

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the join as you've written it is impossible

    the "1" subquery does not produce a DEPTNO column, so you can't join on that

    why are you taking DISTINCT COLA,COLB?

    what are COLA, COLB?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2011
    Posts
    2

    Further clarification

    Hi,

    If you look at the data I provided.

    I have four attributes EMPLOYEENO, EMP NAME, DEPT NO , SALARY

    With one read on this table I want to sum the Salaries of people in the same department and display it for every employee in it.

    Result should be like:
    EMP NO, EMP NAME, DEPT NO, SUM(SALARY) grouped by DEPT NO

    Regards
    Partha

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't think you can do that "with one read on this table" unless you could perhaps use the db2 GROUP BY ROLLUP option
    Code:
    SELECT employeeno
         , empname
         , deptno 
         , SUM(salary)
      FROM employee
    GROUP
        BY ROLLUP(deptno,employeeno,empname)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    One TBSCAN on employee table and two SORT(s).

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     employee
    ( Employee_id , Name , Dept_No , Salary ) AS (
    VALUES
      ( 1000 , 'Q' , 'MECH' ,  500.00 )
    , ( 1001 , 'D' , 'ECE'  , 1000.00 )
    , ( 1002 , 'R' , 'EEE'  ,  502.00 )
    , ( 1003 , 'X' , 'MECH' ,  600.00 )
    , ( 1004 , 'T' , 'EEE'  ,  800.00 )
    , ( 1005 , 'A' , 'ECE'  ,  200.00 )
    , ( 1006 , 'V' , 'ECE'  , 1100.00 )
    , ( 1007 , 'C' , 'EEE'  ,  300.00 )
    , ( 1008 , 'Z' , 'MECH'  , 400.00 )
    , ( 1009 , 'B' , 'MECH'  , 700.00 )
    , ( 1010 , 'N' , 'EEE'   , 150.00 )
    )
    SELECT employee_id
         , name
         , dept_no
         , VARCHAR( TO_CHAR(
              SUM(salary)
                 OVER( PARTITION BY dept_no)
            , '$9,999.00MI'
           ) , 10 ) AS sum_salary
     FROM  employee
     ORDER BY
           employee_id
    ;
    ------------------------------------------------------------------------------
    
    EMPLOYEE_ID NAME DEPT_NO SUM_SALARY
    ----------- ---- ------- ----------
           1000 Q    MECH    $2,200.00 
           1001 D    ECE     $2,300.00 
           1002 R    EEE     $1,752.00 
           1003 X    MECH    $2,200.00 
           1004 T    EEE     $1,752.00 
           1005 A    ECE     $2,300.00 
           1006 V    ECE     $2,300.00 
           1007 C    EEE     $1,752.00 
           1008 Z    MECH    $2,200.00 
           1009 B    MECH    $2,200.00 
           1010 N    EEE     $1,752.00 
    
      11 record(s) selected.
    Last edited by tonkuma; 12-27-11 at 19:38. Reason: replace result column name "salary" by "sum_salary"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •