question

Unknownuser avatar image
Unknownuser 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
joinstablesgroup-bysum
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
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!
1 Like 1 ·
JohnM avatar image JohnM commented ·
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.
1 Like 1 ·
Unknownuser avatar image Unknownuser commented ·
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
0 Likes 0 ·
Unknownuser avatar image Unknownuser commented ·
Hi, It exists in dbo.Viewaccum1day(TheorWin) but I just want to sum up all the values per playerid and location as ClubADW..
0 Likes 0 ·
Unknownuser avatar image Unknownuser commented ·
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
0 Likes 0 ·
Show more comments
JohnM avatar image
JohnM answered
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!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Unknownuser avatar image Unknownuser commented ·
Hi There, Thanks for the reply. Great thanks so much for the pointer. I will give it a go asap.. :)
0 Likes 0 ·
Unknownuser avatar image
Unknownuser answered
Hi John, Yes thats correct
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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!
1 Like 1 ·
Unknownuser avatar image
Unknownuser 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'.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Can you please post the query as you currently have it?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.