Query Help

Partha.kotapati · #1 (**permalink**) 12-27-11, 15:04

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

r937 · #2 (**permalink**) 12-27-11, 15:58

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?

Partha.kotapati · #3 (**permalink**) 12-27-11, 16:30

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

r937 · #4 (**permalink**) 12-27-11, 16:57

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)

tonkuma · #5 (**permalink**) 12-27-11, 18:34

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech