FatalError FatalError - 5 months ago 13
SQL Question

SQL query: how to remove duplication of Service Charge?

I have these three table

ITEM table:

ITEM_ID AMOUNT_DUE SERVICE_CHARGE
-----------------------------------
1 1000 100


TENDER table:

ITEM_ID TYPE_ID AMOUNT
-------------------------------
1 0 600
1 1 500


TYPE table:

TYPE_ID TYPE_NAME
-------------------------------
0 CASH
1 CREDIT


I tried:

select
TYPE_NAME,
sum(AMOUNT) as Amount,
sum(ServiceCharge) as SC
from
TENDER
left join
TYPE on TYPE.TYPE_ID = TENDER.TYPE_ID
left join
ITEM on ITEM.ITEM_ID = TENDER.ITEM_ID
group by
TYPE_NAME


But results have been duplicated for Service Charge like

TYPE_NAME AMOUNT SC
-------------------------------
CASH 650 100
CREDIT 500 100


NOTE: Service Charge is computed * 10

Answer

maybe something like this:

select TYPE_NAME, sum(AMOUNT) as Amount, 
sum(ServiceCharge)/ (COUNT(*) OVER (PARTITION BY TENDER.ITEM_ID) ) as SC 
from TENDER 
LEFT JOIN TYPE ON TYPE.TYPE_ID = TENDER.TYPE_ID
LEFT JOIN ITEM ON ITEM.ITEM_ID = TENDER.ITEM_ID
GROUP BY TYPE_NAME, TENDER.ITEM_ID;