Btukfyl

For each employee in a database, I'm trying to produce a list of the total number of items they have sold which have generated a set amount of profit (if they have sold any), i.e.

+--------------+----------+----------+----------+

| EmployeeName | Product1 | Product2 | Product3 |

+--------------+----------+----------+----------+

| John Smith   |        4 |        7 |        1 |

+--------------+----------+----------+----------+

where Product1, Product2 and Product3 represent products with IDs 1, 2 and 3, which happen to have made >x profit.

The solution I have so far is a dynamic pivot where I find the product IDs that have made >x profit, and construct the SELECT and PIVOT clauses using LISTAGG. However, when I run the query on a test dataset, the vast majority of the output consists of empty fields under the Productn columns, and each employee has many entries that aren't grouped together.

I think this is because the input to the pivot is not being filtered to remove products which haven't made >x, but I don't know how I can filter it whilst still using the pivot, since I can't use GROUP BY/HAVING or WHERE.

The query I have so far is:

SELECT FName || '' '' || LName AS EmployeeName, ' || selectClause || ' FROM STAFF_ORDERS

INNER JOIN STAFF ON STAFF_ORDERS.StaffID = STAFF.StaffID

INNER JOIN STAFF_EXP_TOTALS ON STAFF_EXP_TOTALS.StaffID = STAFF_ORDERS.StaffID

INNER JOIN ORDER_PRODUCTS ON STAFF_ORDERS.OrderID = ORDER_PRODUCTS.OrderID

PIVOT (SUM(ProductQuantity) FOR ProductID IN ('|| pivotClause || ')) ORDER BY TotalValueSold DESC

A section of the current output looks like this:

If anyone knows how I can fix the query, or a better way to approach this, that would be a great help. If you need more information, let me know and I'll provide it.

Thanks,

James

For the following queries, a simplified STAFF_ORDERS table and the (sample) HR.EMPLOYEES table have been used (Oracle 12c). Principle: join all required tables, pivot, and filter. Maybe this example will help you -

Test tables and data

create table staff_orders (

  id           number primary key

, employee_id  number

, product_code varchar2( 5 )

, quantity     number

);



insert into staff_orders values ( 1, 101, 'A', 10);

insert into staff_orders values ( 2, 101, 'B', 20);

insert into staff_orders values ( 3, 101, 'C', 30);

insert into staff_orders values ( 4, 102, 'A', 40);

insert into staff_orders values ( 5, 102, 'C', 50);

insert into staff_orders values ( 6, 103, 'A', 60);

insert into staff_orders values ( 7, 103, 'B', 70);

insert into staff_orders values ( 8, 103, 'C', 80);

insert into staff_orders values ( 9, 103, 'D', 90);

insert into staff_orders values (10, 104, 'A', 100);

insert into staff_orders values (11, 101, 'A', 10);

insert into staff_orders values (12, 101, 'B', 20);

insert into staff_orders values (13, 101, 'C', 30);

insert into staff_orders values (14, 102, 'A', 40);

insert into staff_orders values (15, 102, 'C', 50);

insert into staff_orders values (16, 103, 'A', 60);

insert into staff_orders values (17, 103, 'B', 70);

insert into staff_orders values (18, 103, 'C', 80);

insert into staff_orders values (19, 103, 'D', 90);

insert into staff_orders values (20, 104, 'A', 100);

commit;

Table STAFF_ORDERS

select * from staff_orders;



        ID EMPLOYEE_ID PRODU   QUANTITY

---------- ----------- ----- ----------

         1         101 A             10

         2         101 B             20

         3         101 C             30

         4         102 A             40

         5         102 C             50

         6         103 A             60

         7         103 B             70

         8         103 C             80

         9         103 D             90

        10         104 A            100

-- etc...

Table HR.EMPLOYEES

select employee_id, first_name, last_name

from hr.employees

where employee_id in ( 101, 102, 103, 104 ) ;



EMPLOYEE_ID  FIRST_NAME  LAST_NAME  

102          Lex         De Haan    

104          Bruce       Ernst      

103          Alexander   Hunold     

101          Neena       Kochhar 

Join the tables and adjust the column(s) as required.

select 

  E.employee_id

, E.first_name || ' ' || E.last_name as employee_name

, O.product_code

, O.quantity

from hr.employees E

  join staff_orders O on E.employee_id = O.employee_id ;



EMPLOYEE_ID  EMPLOYEE_NAME     PRODUCT_CODE  QUANTITY  

101          Neena Kochhar     C             30        

101          Neena Kochhar     B             20        

101          Neena Kochhar     A             10        

101          Neena Kochhar     C             30        

101          Neena Kochhar     B             20        

101          Neena Kochhar     A             10        

102          Lex De Haan       C             50        

-- ...

103          Alexander Hunold  C             80        

103          Alexander Hunold  B             70        

103          Alexander Hunold  A             60        

104          Bruce Ernst       A             100       

104          Bruce Ernst       A             100  

Pivot -> sum of quantities.

select *

from (

  select 

    E.employee_id

  , E.first_name || ' ' || E.last_name as employee_name

  , O.product_code

  , O.quantity

  from hr.employees E

    join staff_orders O on E.employee_id = O.employee_id

)

pivot  (  

  sum( quantity ) as total for ( product_code ) in (

    'A' AS product_a

  , 'B' AS product_b

  , 'C' AS product_c

  )

)

;



EMPLOYEE_ID  EMPLOYEE_NAME     PRODUCT_A_TOTAL  PRODUCT_B_TOTAL  PRODUCT_C_TOTAL  

104          Bruce Ernst       200              NULL             NULL             

102          Lex De Haan       80               NULL             100              

103          Alexander Hunold  120              140              160              

101          Neena Kochhar     20               40               60 

Filter: product total > 100 (add a WHERE clause to the previous query).

select *

from (

  select 

    E.employee_id

  , E.first_name || ' ' || E.last_name as employee_name

  , O.product_code

  , O.quantity

  from hr.employees E

    join staff_orders O on E.employee_id = O.employee_id

)

pivot  (  

  sum( quantity ) as total for ( product_code ) in (

    'A' AS product_a

  , 'B' AS product_b

  , 'C' AS product_c

  )

)

where product_a_total > 100

   or product_b_total > 100  -- use AND here if need be

   or product_c_total > 100  -- use AND here if need be 

;



EMPLOYEE_ID  EMPLOYEE_NAME     PRODUCT_A_TOTAL  PRODUCT_B_TOTAL  PRODUCT_C_TOTAL  

104          Bruce Ernst       200              NULL             NULL             

103          Alexander Hunold  120              140              160