Need help on SQL Query

Posted on 2011-10-04
Last Modified: 2012-05-12
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

Question by:dprasannain
    LVL 9

    Accepted Solution

    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


    Author Closing Comment

    Had to do some modification in the query make it work as i wanted

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now