• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Need help on SQL Query

ItemID  ItemName    ItemPrice
-----------------------------
-----------------------------
101      McDonald's    90


UID  ItemID   UserId  ContributedAmount  
------------------------------------
------------------------------------
1     101       U001           50
2     101       U002           40
3     101       U003            0
4     101       U004            0

I have two tables shown as above , 4 Persons go to a restaurant and the total bill was 90$
Person1 pays 50$ and Person2 pays 40$ and other 2 persons didnt pay at all.
The paid amount should be shared equally among 4, Now i need a query to find out who needs to pay
to whom of how many amount ($'s) ?  Help would be appreciated

Regards
Prasanna
0
dprasannain
Asked:
dprasannain
1 Solution
 
sachinpatil10dCommented:
Try this if it works for you.
select UID, test.ItemID,UserID,ContributedAmount, convert(decimal,Total)/convert(decimal,TotalCount) EachContri
, ContributedAmount - (convert(decimal,Total)/convert(decimal,TotalCount)) pendingAmount
from test inner join 
(select itemid,SUM(ContributedAmount) Total,COUNT(*) TotalCount  from test group by itemid) t
on test.itemid = t.itemid

Open in new window

0
 
dprasannainAuthor Commented:
Had to do some modification in the query make it work as i wanted
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now