CODEWITHSUNDEEP
sqloracle-databasepivotoracle12c
jamerst
jamerst

Reputation: 95

Oracle SQL Pivot query not grouping correctly

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:current output

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

Upvotes: 1

Views: 237

Answers (1)

stefan
stefan

Reputation: 2252

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

Upvotes: 1

Related Questions