Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    27

    Question Unanswered: SQL HELP with SUM and Casing..

    Hi All,

    Im a little lost on this one, hope you can help I have 2 Views im joining them and I was advised to like as below....

    The problem is I can easily get rows required from the first table "ViewCust" with the Custno,Title, First,Surname but or P but not the summed up values for each record..

    So as an example if I have this:
    CustID Title First Name Surname TRIPS MONEY BALANCE
    12345678 Mr BOB BUILDER 1 20 100
    12345678 Mr BOB BUILDER 3 123 220
    12345678 Mr BOB BUILDER 4 142 145
    12345699 MISS SARAH SARAH 7 231 57
    12345611 MRS BILLY Unknown 2 15 65
    12345622 MR MIKE JONES 1 10 100

    I would like to just get the folowing: Proper Cased
    12345678 Mr Bob Builder 7 285 465
    12345699 Miss Sarah Sarah 7 231 57
    12345611 Mrs Billy Unknown 2 15 65
    12345622 Mr Mike Jones 1 10 100


    This is the statement:

    Select Distinct
    P.CustID, P.Acct, P.Title, P.FirstName, P.LastName
    FROM dbo.viewCust P
    INNER JOIN
    (SELECT CustID, Sum(ActionDays) as "Trips",
    SUM(MoneySpent) as "MoneySpent", Sum(Balance) as Bal
    FROM dbo.viewCAsh
    Where Accumulator='DP'
    GROUP BY
    CustID) A
    ON
    P.CustID= A.CustID

    I have about another 42 fields but if I can get this then the other will be quite similar. Thanks For your help..

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    Select Distinct 
        P.CustID, 
        P.Acct, 
        P.Title, 
        P.FirstName, 
        P.LastName,
        A.Trips,
        A.MoneySpent,
        A.Bal
    FROM dbo.viewCust P
    INNER JOIN
        (SELECT 
             CustID, 
             Sum(ActionDays) as Trips,
             SUM(MoneySpent) as MoneySpent, 
             Sum(Balance) as Bal 
         FROM dbo.viewCAsh 
         Where Accumulator='DP'
         GROUP BY CustID) A ON P.CustID = A.CustID
    Hope this helps.

Posting Permissions

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