asked
SQL query help :)
Hi all, Can anyone help please I'm happy joining 2 table etc but how do I add in a 3rd-4th table to a query below? I needed was to get the sums and group by of the 3rd and 4th table... The 2 new tables are: ViewDis (X) - Field Sum (Distance) ViewACC (Y) - Fields Sum (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 Im not sure if the inner join is in the correct place and if I reference the fields correctly. Thanks for you help. Sql Newbie
The column 'ClubADW' doesn't exist in the dbo.ViewAccum1Day view or table, thus the error. Can you confirm that the column is present in the table or view? If you were meaning to infer that column from the correlated subquery, you'll need to change the naming from 'X.ClubADW' to 'A.ClubAdw' in order to use that column. Hope this helps!
Then as I mentioned previously, you'll need to adjust your query to either pull 'A.ClubADW' (the alias in the subquery) or 'X.TheorWin' which is the physical column name in the dbo.ViewAccum1Day table. In either case, that should resolve the error that you are currently seeing.
Hi there, No Its didnt work, I got the go ahead from powers that be to post the script with a few changes so here it is.....Along with the error message! > Msg 207, Level 16, State 1, Line 12 > Invalid column name 'ClubADW'. 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
Hi, It exists in dbo.Viewaccum1day(TheorWin) but I just want to sum up all the values per playerid and location as ClubADW..
Hi, I understand but It does exists in dbo.Viewaccum1day(TheorWin) but I just want to sum up all the values per playerid and location as ClubADW.. Sum (TheorWin) as ClubADW
Without having some data to play with, I'd say that your query is close. In the INNER JOIN's you specified a 'v1' & 'v2', however they don't correlate to any known alias in your query that I can see. My guess is that you would need to change the query to something like this: 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 X.CustID = A.CustID INNER JOIN dbo.viewAcc Y ON Y.CustID = A.CustID This would join the new tables to correlated query, however depending on your data and what the results need to look like, you might need to adjust this accordingly. Hope this helps!
Hi There, Thanks for the reply. Great thanks so much for the pointer. I will give it a go asap.. :)
Hi John, Yes thats correct
There should be a "tick" you can click next to the beginning of John's answer. Please click it to mark the question as answered!
Hi John, TheorWin is in both tables but for the specific task I need the sum of the X.theorwin. I used the physical name X.theorwin but now faced with -- Msg 8155, Level 16, State 2, Line 2 No column was specified for column 32 of 'A'.
6 People are following this question.
