CODEWITHSUNDEEP
sqlsql-serverdatabasejoinaggregate-functions
Faisal Fahad
Faisal Fahad

Reputation: 3

How do I retrieve data from multiple aggregation tables?

I have 3 table for payment methods (credit card -SADAD- at_place).

I retrieve the data from all of them and I use Sum to sum the prices for all 

select Payment_Date, count(Payment_Date) as Transaction_COUNT, sum(OWNER_Amount) as OWNER_Amount  , sum (commission) as commission,sum(Total_Amount) As Sub_total
  from (
select format(PAYMENT_POOL_CREDIT.PAYMENT_POOL_CREDIT_DATE,'dd/MM/yyyy') as Payment_Date,PAYMENT_POOL_CREDIT.PAYMENT_POOL_CREDIT_OWNER_MONEY as OWNER_Amount,PAYMENT_POOL_CREDIT.PAYMENT_POOL_CREDIT_TAX_MONEY as commission, PAYMENT_POOL_CREDIT.PAYMENT_POOL_CREDIT_OWNER_MONEY+PAYMENT_POOL_CREDIT.PAYMENT_POOL_CREDIT_TAX_MONEY as Total_Amount from PAYMENT_POOL_CREDIT
union  all
select format(PAYMENT_POOL_SADAD.PAYMENT_POOL_SADAD_DATE,'dd/MM/yyyy') as Payment_Date,PAYMENT_POOL_SADAD.PAYMENT_POOL_SADAD_OWNER_MONEY as OWNER_Amount,PAYMENT_POOL_SADAD.PAYMENT_POOL_SADAD_TAX_MONEY as commission ,PAYMENT_POOL_SADAD.PAYMENT_POOL_SADAD_OWNER_MONEY+PAYMENT_POOL_SADAD.PAYMENT_POOL_SADAD_TAX_MONEY as Total_Amount  from PAYMENT_POOL_SADAD
union all 
select format(PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_DATE,'dd/MM/yyyy') as Payment_Date,PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_OWNER_MONEY as OWNER_Amount,PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_TAX_MONEY as commission,PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_OWNER_MONEY+PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_TAX_MONEY  as Total_Amount from PAYMENT_POOL_AT_PLACE
 ) as t

group by Payment_Date

Now what I want is to join another aggregation column to the previous query from this aggregation. 

select format(PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_DATE,'dd/MM/yyyy') as Payment_Date, sum(PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_OWNER_MONEY+PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_TAX_MONEY)  as Total_Amount_At_Place from PAYMENT_POOL_AT_PLACE
    group by  format(PAYMENT_POOL_AT_PLACE.PAYMENT_POOL_AT_PLACE_DATE,'dd/MM/yyyy')

Can anyone help?

this is sample tables

PAYMENT_POOL_CREDIT

Payment_Date | OWNER_Amount  |  commission | Total_Amount 
11/02/2017   |    500.00     |    40.00    |    540.00
15/05/2016   |    242.00     |    10.00    |    252.00
11/02/2017   |    100.00     |    30.00    |    130.00
15/05/2016   |    620.00     |    60.00    |    680.00

PAYMENT_POOL_SADAD

Payment_Date | OWNER_Amount  |  commission | Total_Amount 
05/05/2016   |    5000.00    |  200.00     |    5200.00
11/02/2017   |    242.00     |    10.00    |    252.00
15/05/2016   |    430.00     |    30.00    |    460.00
11/02/2017   |    310.00     |    60.00    |    370.00
15/05/2016   |    220.00     |    60.00    |    280.00

PAYMENT_POOL_AT_PLACE

Payment_Date | OWNER_Amount  |  commission | Total_Amount 
17/06/2016   |    2000.00    |  300.00     |    2300.00
15/05/2016   |    500.00     |   200.00    |    700.00
22/06/2016   |    500        |    300.00   |    800.00
17/06/2016   |    2000.00    |    300.00   |    2300.00
15/05/2016   |    500.00     |    200.00   |    700.00

the result I am looking for is look like this

Payment_Date |  Transaction_COUNT  | OWNER_Amount  | Total_commission  |  Total_Amount  |  Total_at_palce
05/05/2016   |      1              |    5000.00    |    200.00         |    5200.00     |  NULL
11/02/2017   |      4              |    1052.00    |    140.00         |    1192.00     |  NULL
15/05/2016   |      6              |    2512.00    |    590.00         |    3102.00     |  1400
22/06/2016   |      1              |    500.00     |    300.00         |    800.00      |  800

Upvotes: 0

Views: 39

Answers (1)

SqlZim
SqlZim

Reputation: 38023

Instead of joining those queries, you can add a column to your union all derived table to differentiate between sources, so you can use conditional aggregation to also get the total_amount from payment_pool_at_place separately, like so:

select Payment_Date
  , count(Payment_Date) as Transaction_count
  , sum(owner_Amount) as owner_Amount
  , sum(commission) as commission
  , sum(Total_Amount) as Sub_total
  , sum(case when src = 'ppap' then Total_Amount_At_Place end) as Total_Amount_At_Place
from (
   select format(ppc.payment_pool_credit_date, 'dd/mm/yyyy') as Payment_Date
    , ppc.payment_pool_credit_owner_money as owner_Amount
    , ppc.payment_pool_credit_tax_money as commission
    , ppc.payment_pool_credit_owner_money 
      + ppc.payment_pool_credit_tax_money as Total_Amount
    , src = convert(varchar(4),'ppc')
   from payment_pool_credit as ppc
  union all
  select format(pps.payment_pool_sadad_date, 'dd/mm/yyyy') as Payment_Date
    , pps.payment_pool_sadad_owner_money as owner_Amount
    , pps.payment_pool_sadad_tax_money as commission
    , pps.payment_pool_sadad_owner_money 
      + pps.payment_pool_sadad_tax_money as Total_Amount
    , src = convert(varchar(4),'pps')
  from payment_pool_sadad as pps
  union all
  select format(ppap.payment_pool_at_place_date, 'dd/mm/yyyy') as Payment_Date
    , ppap.payment_pool_at_place_owner_money as owner_Amount
    , ppap.payment_pool_at_place_tax_money as commission
    , ppap.payment_pool_at_place_owner_money 
      + ppap.payment_pool_at_place_tax_money as Total_Amount
    , src = convert(varchar(4),'ppap')
  from payment_pool_at_place as ppap
 ) as t
group by Payment_Date

Updated for the new samples: 

select PaymentDate
  , count(Payment_Date) as Transaction_count
  , sum(owner_Amount) as owner_amount
  , sum(commission) as commission
  , sum(Total_Amount) as Sub_total
  , sum(case when src = 'ppap' then Total_Amount end) as Total_Amount_At_Place
from (
   select Payment_Date
    , ppc.owner_amount as owner_Amount
    , ppc.tax_amount as commission
    , ppc.owner_amount 
      + ppc.tax_amount as Total_Amount
    , src = convert(varchar(4),'ppc')
   from payment_pool_credit as ppc
  union all
  select Payment_Date
    , pps.owner_amount as owner_Amount
    , pps.tax_amount as commission
    , pps.owner_amount 
      + pps.tax_amount as Total_Amount
    , src = convert(varchar(4),'pps')
  from payment_pool_sadad as pps
  union all
  select Payment_Date
    , ppap.owner_amount as owner_Amount
    , ppap.tax_amount as commission
    , ppap.owner_amount 
      + ppap.tax_amount as Total_Amount
    , src = convert(varchar(4),'ppap')
  from payment_pool_at_place as ppap
 ) as t
 where Payment_Date != '20160617' /* this is missing from your desired results */
group by Payment_Date
order by format(payment_date,'dd/MM/yyyy') /* to match desired results order */

returns:

+-------------+-------------------+--------------+------------+-----------+-----------------------+
| PaymentDate | Transaction_count | owner_amount | commission | Sub_total | Total_Amount_At_Place |
+-------------+-------------------+--------------+------------+-----------+-----------------------+
| 2016-05-05  |                 1 | 5000.00      | 200.00     | 5200.00   | NULL                  |
| 2017-02-11  |                 4 | 1152.00      | 140.00     | 1292.00   | NULL                  |
| 2016-05-15  |                 6 | 2512.00      | 560.00     | 3072.00   | 1400.00               |
| 2016-06-22  |                 1 | 500.00       | 300.00     | 800.00    | 800.00                |
+-------------+-------------------+--------------+------------+-----------+-----------------------+

Upvotes: 1

Related Questions