SQL/DB2 getting single row of results per employee with a UNION-CodePudding

I'm currently using a UNION on 2 select statements and while I'm getting the correct data, it's not exactly what I actually need when it comes time to use it in a front-end view

I'm currently using this query:

SELECT
    T.employee as employee,
    'Orders' as TYPE,
    SUM(CASE WHEN t.order_count < QUANT THEN t.order_count ELSE QUANT END) as DATA
FROM schemaOne.order_list T
 WHERE t.order_date > CURRENT_DATE - 35 DAYS 
group by t.employee


UNION

select
       T.employee as employee,
       'Sales' as TYPE,
       sum(price * quant) as DATA
from schemaOne.sales T 
WHERE T.sales_date > CURRENT_DATE - 35 DAYS
group by T.employee
order by data desc;

with these dummy tables as examples and getting the following result:

order_list

employee  |  order_count  |  quant  |  order_date
--------------------------------------------------
123       |   5           |    1    |   2022-03-02
456       |   1           |    5    |   2022-03-02


sales

employee  |  price        |  quant  |  order_date
--------------------------------------------------
123       |   500         |    1    |   2022-03-02
456       |   1000        |    1    |   2022-03-02

Result

employee       |      type     |     data
------------------------------------------
123                Orders            1
123                Sales             500
456                Orders            5
456                Sales             1000

Is there a way to use a UNION but alter it so that I can instead get a single row for each employee and just get rid of the type/data columns and instead set each piece of data to the desired column (the type would instead be the column name ) like so:

Desired Result

employee   |   Orders   |   Sales  
---------------------------------
123        |     1      |    500
456        |     5      |    1000

CodePudding user response：

Try adding an outer query:

select employee, 
       MAX(case when type=Orders then data end) as orders ,
       MAX(case when type=Sales then data end) as  Sales 
from (
       SELECT  T.employee as employee,
               'Orders' as TYPE,
               SUM(CASE WHEN t.order_count < QUANT THEN t.order_count ELSE QUANT END) as DATA
       FROM schemaOne.order_list T
       WHERE t.order_date > CURRENT_DATE - 35 DAYS 
      group by t.employee

   UNION
      
       select  T.employee as employee,
              'Sales' as TYPE,
              sum(price * quant) as DATA
       from schemaOne.sales T 
       WHERE T.sales_date > CURRENT_DATE - 35 DAYS
       group by T.employee
    ) as t1
GROUP BY employee;

Note that I removed order by data desc it has no effect inside the union

CodePudding user response：

You can join tables through employee columns such as

SELECT o.employee,
       SUM(CASE
             WHEN o.order_count < o.quant THEN
              o.order_count
             ELSE
              o.quant
           END) AS Orders,
       SUM(s.price * s.quant) AS Sales   
  FROM schemaOne.order_list o
  JOIN schemaOne.sales s
    ON s.employee = o.employee
   AND s.sales_date = o.order_date
 WHERE o.order_date > current_date - 35 DAYS
 GROUP BY o.employee