Results 1 to 15 of 29
Thread: Join and Sum Query

053012, 05:16 #1Registered User
 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

053012, 10:13 #2Registered User
 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'

053012, 12:07 #3Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54My first guess would be that there was no distance column in the CAsh table.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

053012, 12:15 #4Registered User
 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..

053012, 12:21 #5Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Please post the CREATE TABLE definitions for your Cust and CAsh tables.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

053012, 12:29 #6Registered User
 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

053012, 13:18 #7Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54The 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?
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

053112, 05:47 #8Registered User
 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'.

053112, 09:16 #9Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Should the X.ClubADW in your SELECT list be an A.ClubAdw instead?
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

053112, 10:32 #10Registered User
 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

053112, 10:37 #11Registered User
 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'.

053112, 10:38 #12Registered User
 Join Date
 Mar 2012
 Posts
 27
Physical name as X.theorwin

060112, 10:28 #13Registered User
 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

060112, 10:41 #14Registered User
 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

060112, 10:42 #15Registered User
 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'.