Results 1 to 2 of 2

Thread: SQL Issue

  1. #1
    Join Date
    Apr 2009
    Posts
    9

    Unanswered: SQL Issue

    Hi, I have a problem here. The following is my table data.

    CustomerID Course Total Used Spent
    C1 A1 1500 180 190
    C1 A1 1500 0 900
    C1 B2 1800 500 400
    C1 B2 1800 200 200

    What I need to get is as the following:

    CustomerID Total Used Spent Balance
    C1 3300 880 1690 730

    Where 3300 is get from two course distinct total which is 1500+3300. Used is get from all Used data and Spent is get from all Spent data. The balance should be Total-Used-Spent.

    How should I write my sql so that it will determine if there are same course with different used and spent, then i should only get a total but not two duplicate total?

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    i tried this query. not sure whether this meets ur requirement.


    Declare @vt_Table Table
    (
    customerid varchar(2),
    course varchar(2),
    total int,
    used int,
    spent int
    )

    insert into @vt_Table
    (
    customerid ,
    course ,
    total ,
    used ,
    spent
    )
    Select 'C1', 'A1', 1500, 180, 190
    union
    Select 'C1', 'A1', 1500, 0, 900
    union
    Select 'C1', 'B2', 1800, 500, 400
    union
    Select 'C1', 'B2', 1800, 200, 200


    Select Customerid,
    sum (distinct total) as disttotal,
    sum(used) as usedtotal,
    sum(spent) as spenttotal ,
    sum (distinct total)-
    sum(used) -
    sum(spent) as balance
    from @vt_Table
    group by Customerid
    Cheers....

    baburajv

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •