Joining two tables based on two Aggregate columns

aggregatejoin;oracle

I have a following problem as in the example. I have to join two tables based on the aggregated columns. These are the only way to combine these two tables. Is it possible to do this? I have tried Having by – procedures, without any success.

Select a.customerID,
sum(a.Items) as Amount a
sum(b.Items) as Amount b
From Orders a

Join Table B
   on SUM(a.Items) = SUM(b.Items) 
Group By a.CustomerID

Best Answer

Something like this:

with 
ta as (select a.customerid, sum(a.items) amount_a
       from orders a
       group by a.customerid
      ),
tb as (select sum(b.items) amount_b
       from tableb b
      )
select ta.customerid, ta.amount_a, tb.amount_b
from ta join tb on ta.amount_a = tb.amount_b

Related Solutions

Join multiple Tables by date

You could join to the 2 tables separately (and GROUP BY) and then join them together:

WITH dates (dat) AS
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) 
, ret (dat, return_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(r.return_amount), 0) AS return_amount
      FROM dates  d
        LEFT JOIN returns  r ON r.RETURN_DATE = d.dat
      GROUP BY
        d.dat
    )
, ord (dat, order_amount) AS
    ( SELECT
        d.dat,
        COALESCE(SUM(o.order_amount), 0) AS order_amount
      FROM dates  d
        LEFT JOIN orders  o ON o.ORDER_DATE = d.dat
      GROUP BY
        d.dat
    )
SELECT
  r.dat,
  r.return_amount,
  o.order_amount
FROM
  ret  r
    JOIN ord  o ON o.dat = r.dat
ORDER BY 
  r.dat ;

Or use inline subqueries:

SELECT
  d.dat,
  COALESCE( ( SELECT SUM(r.return_amount) 
              FROM returns  r 
              WHERE r.dat = d.dat
            ), 0) 
    AS return_amount,
  COALESCE( ( SELECT SUM(o.order_amount) 
              FROM orders  o 
              WHERE o.dat = d.dat
            ), 0) 
    AS order_amount
FROM
    ( SELECT
        TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat
      FROM 
        all_objects
      WHERE 
        ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy')
                  - TO_DATE('02.05.2014','dd.mm.yyyy') + 1
    ) d
ORDER BY 
  d.dat ;

Need help joining two tables with a having count clause

very close only the GROUP BY Columns forgotten

Try this:

select 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
from PTC_DIAGNOSIS 
inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID
where PTC_DIAGNOSIS.create_date>'12/01/2015' 
group by 
PTC_DIAGNOSIS.PATIENT_ID, 
PT_BASIC.PATIENT_CODE, 
PT_BASIC.NAME_FIRST, 
PT_BASIC.NAME_LAST 
having COUNT(*)=1

Best Answer

Related Solutions

Join multiple Tables by date

Need help joining two tables with a having count clause

Related Question