Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Mar 2012
    Posts
    27

    Unanswered: Join and Sum Query


    Hi all,

    This code works well but how do I add in a 3rd and 4 table to the mix and get their sums and group by ...The top select works fine the lower one is what I was thinking is this.....

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


    The 2 new tables are ViewDis (X) - Sum (Distance)
    and ViewACC (Y) Sum Fields required Hours, Min, (Group by) Building and Date.

    Select Distinct
    P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
    A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building
    FROM dbo.Cust P
    INNER JOIN
    (SELECT
    CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent,
    Sum(Balance) as Bal, Sum(Distance) as Dist, Sum(Hours), Sum(Min)
    FROM dbo.CAsh
    Where Accumulator='DP'
    GROUP BY CustID) A ON P.CustID = A.CustID
    INNER JOIN dbo.viewDis X ON v1.CustID = A.CustID
    INNER JOIN dbo.viewAcc Y ON v2.CustID = A.CustID

  2. #2
    Join Date
    Mar 2012
    Posts
    27

    Any Ideas?? Please

    Anyone know if the synthax is wrong as I keep getting errors...
    such as this --

    Msg 207, Level 16, State 1, Line 4
    Invalid column name 'Distance'

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be that there was no distance column in the CAsh table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2012
    Posts
    27
    Hi There,

    The field is definatley in the data and I have referenced it in both the select and the inner join..

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please post the CREATE TABLE definitions for your Cust and CAsh tables.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Mar 2012
    Posts
    27
    Hi There,

    OK This is the statement below: Quite different from the original but got the OK to post from those above me... Thanks

    Select distinct
    P.PlayerID, P.Acct, P.Title, P.FirstName, P.LastName, P.HomeAddr1, P.HomeAddr2, P.HomeCity,
    P.HomePostalCode, P.HomeCountryCode, P.HomeTel1Type, P.HomeTel1, P.HomeTel2Type, P.HomeTel2,
    P.IsSMSSend, P.HomeEmail, P.IsEmailSend, P.BirthDt, P.TypeID, P.IsVIP,
    P.IsBanned, P.IsProblemGambler, P.IsNoMail,P.IsReturnMail, P.IsMailToAlt, P.Sex,
    P.SetupCasinoID, P.SetupDtm, P.MaxTrpCasinoId, P.PtsBal, P.LastTripDt,
    P.LTDPts, P.HostEmpID, P.SetupEmpID, P.LastEditDtm, P.LastEditEmpID, P.PlayerID,
    P.GroupID,
    A.ActionD, A.TheorWin, A.CashbuyIn, A.ChipBuyIn, A.BuyIn, A.CasinoWin, A.Bet, A.EarnedPts, A.BonusPts,
    A.RedeemPts, A.AdjPtsDr, A.AdjPtsCr, A.NetPts, A.EarnedComp, A.AdjCompDr, A.AdjCompCr, A.CompSpent, A.Unusedcomp,
    A.RatingPeriodMinutes, A.PlayTimeHours, A.AvgBetPerPlay, A.Trips, A.AuthAward, A.AwardUsed, A.UnsettledAward,
    A.Food, A.Travel, A.Merchandise, A.OtherAward, A.CompUsed, A.CasinoID, X.ClubADW
    FROM dbo.ViewCustomers P
    INNER JOIN
    (SELECT PlayerID, Sum(ActionDays) as ActionD, SUM(TheorWin) as TheorWin,
    Sum(CashBuyIn) as CashbuyIn, Sum(ChipBuyIn) as ChipBuyIn, Sum(BuyIn) as BuyIn,
    Sum(CasinoWin) as CasinoWin,Sum(Bet) as Bet, Sum(EarnedPts) as EarnedPts,
    Sum(BonusPts) as BonusPts,Sum(RedeemPts) as RedeemPts, Sum(AdjPtsDr) as AdjPtsDr,
    Sum(AdjPtsCr) as AdjPtsCr, Sum(NetPts) as NetPts, Sum(EarnedComp) as EarnedComp,
    Sum(AdjCompDr) as AdjCompDr, Sum(AdjCompCr) as AdjCompCr, Sum (CompSpent) as CompSpent,
    Sum(UnusedComp) as Unusedcomp, Sum(RatingPeriodMinutes) as RatingPeriodMinutes,
    Sum(PlayTimeHours) as PlayTimeHours, Sum(AvgBetPerPlay) as AvgBetPerPlay, Sum(Trips) as Trips,
    Sum(AuthAward) as AuthAward, Sum(AwardUsed) as AwardUsed, Sum(UnsettledAward) as UnsettledAward,
    Sum(Food) as Food, Sum(Travel) as Travel, Sum(Merchandise) as Merchandise,
    Sum(OtherAward) as OtherAward, Sum(CompUsed) as CompUsed, Sum (TheorWin) as ClubADW, CasinoID
    FROM dbo.viewAccum1
    Where Accumulator='DP'
    GROUP BY PlayerID, CasinoID) A ON P.PlayerID = A.PlayerID
    left JOIN dbo.ViewAccum1Day X ON A.PlayerID = X.PlayerID

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The SELECT statement that you're actually using will help in the next step.

    There isn't any "Distance" column in the query that you just posted, so what error message or messages are you getting using this query?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2012
    Posts
    27
    Hi Pat,

    Here is teh error message that i was getting..
    Msg 207, Level 16, State 1, Line 13 Invalid column name 'ClubADW'.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Should the X.ClubADW in your SELECT list be an A.ClubAdw instead?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Mar 2012
    Posts
    27
    Hi Pat,

    I did think of that the problem is TheorWin is in both tables but for the specific task I need the sum of the X.clubadw

  11. #11
    Join Date
    Mar 2012
    Posts
    27
    I used the physical name of the actual table and Now I get this...

    Msg 8155, Level 16, State 2, Line 2
    No column was specified for column 32 of 'A'.

  12. #12
    Join Date
    Mar 2012
    Posts
    27
    Physical name as X.theorwin

  13. #13
    Join Date
    Jul 2011
    Posts
    22
    Post the SQL you had when you got:
    Msg 8155, Level 16, State 2, Line 2
    No column was specified for column 32 of 'A

    Also, in this statement, where is v1 and v2 identified? Shouldn't those be X and Y?
    Code:
    Select Distinct
    P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
    A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building
    FROM dbo.Cust P
    INNER JOIN
    (SELECT
    CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent,
    Sum(Balance) as Bal, Sum(Distance) as Dist, Sum(Hours), Sum(Min)
    FROM dbo.CAsh
    Where Accumulator='DP'
    GROUP BY CustID) A ON P.CustID = A.CustID
    INNER JOIN dbo.viewDis X ON v1.CustID = A.CustID
    INNER JOIN dbo.viewAcc Y ON v2.CustID = A.CustID

  14. #14
    Join Date
    Mar 2012
    Posts
    27
    Hi James,

    Please see the code below:

    Use cmktdb
    SELECT
    P.PlayerID,
    P.Acct,
    P.Title,
    P.FirstName,
    P.LastName,
    P.HomeAddr1,
    P.HomeAddr2,
    P.HomeCity,
    P.HomePostalCode,
    P.HomeCountryCode,
    P.HomeTel1Type,
    P.HomeTel1,
    P.HomeTel2Type,
    P.HomeTel2,
    P.IsSMSSend,
    P.HomeEmail,
    P.IsEmailSend,
    P.BirthDt,
    P.TypeID,
    P.IsVIP,
    P.IsBanned,
    P.IsProblemGambler,
    P.IsNoMail,
    P.IsReturnMail,
    P.IsMailToAlt,
    P.Sex,
    P.SetupCasinoID,
    P.SetupDtm,
    P.MaxTrpCasinoId,
    P.PtsBal,
    P.LastTripDt,
    P.LTDPts,
    P.HostEmpID,
    P.SetupEmpID,
    P.LastEditDtm,
    P.LastEditEmpID,
    P.PlayerID,
    P.GroupID,
    A.ActionD,
    A.TheorWin,
    A.CashbuyIn,
    A.ChipBuyIn,
    A.BuyIn,
    A.CasinoWin,
    A.Bet,
    A.EarnedPts,
    A.BonusPts,
    A.RedeemPts,
    A.AdjPtsDr,
    A.AdjPtsCr,
    A.NetPts,
    A.EarnedComp,
    A.AdjCompDr,
    A.AdjCompCr,
    A.CompSpent,
    A.Unusedcomp,
    A.RatingPeriodMinutes,
    A.PlayTimeHours,
    A.AvgBetPerPlay,
    A.Trips,
    A.AuthAward,
    A.AwardUsed,
    A.UnsettledAward,
    A.Food,
    A.Travel,
    A.Merchandise,
    A.OtherAward,
    A.CompUsed,
    A.CasinoID,
    X.Theorwin
    FROM
    dbo.viewPlayers P
    INNER JOIN
    (
    SELECT
    PlayerID,
    Sum(ActionDays) AS ActionD,
    Sum(TheorWin) AS TheorWin,
    Sum(CashBuyIn) AS CashbuyIn,
    Sum(ChipBuyIn) AS ChipBuyIn,
    Sum(BuyIn) AS BuyIn,
    Sum(CasinoWin) AS CasinoWin,
    Sum(Bet) AS Bet,
    Sum(EarnedPts) AS EarnedPts,
    Sum(BonusPts) AS BonusPts,
    Sum(RedeemPts) AS RedeemPts,
    Sum(AdjPtsDr) AS AdjPtsDr,
    Sum(AdjPtsCr) AS AdjPtsCr,
    Sum(NetPts) AS NetPts,
    Sum(EarnedComp) AS EarnedComp,
    Sum(AdjCompDr) AS AdjCompDr,
    Sum(AdjCompCr) AS AdjCompCr,
    Sum (CompSpent) AS CompSpent,
    Sum(UnusedComp) AS Unusedcomp,
    Sum(RatingPeriodMinutes) AS RatingPeriodMinutes,
    Sum(PlayTimeHours) AS PlayTimeHours,
    Sum(AvgBetPerPlay) AS AvgBetPerPlay,
    Sum(Trips) AS Trips,
    Sum(AuthAward) AS AuthAward,
    Sum(AwardUsed) AS AwardUsed,
    Sum(UnsettledAward) AS UnsettledAward,
    Sum(Food) AS Food,
    Sum(Travel) AS Travel,
    Sum(Merchandise) AS Merchandise,
    Sum(OtherAward) AS OtherAward,
    Sum(CompUsed) AS CompUsed,
    Sum(TheorWin),
    CasinoID
    FROM
    dbo.viewAccum1
    WHERE
    Accumulator = 'DP'
    GROUP BY
    PlayerID,
    CasinoID
    ) A
    ON P.PlayerID = A.PlayerID
    LEFT JOIN
    dbo.ViewAccum1Day X
    ON P.PlayerID = X.PlayerID

  15. #15
    Join Date
    Mar 2012
    Posts
    27
    Error Message =

    Msg 8155, Level 16, State 2, Line 2
    No column was specified for column 32 of 'A'.

Posting Permissions

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