# Thread: Join and Sum Query

1. Registered 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

2. Registered User
Join Date
Mar 2012
Posts
27

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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
My first guess would be that there was no distance column in the CAsh table.

-PatP

4. Registered 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..

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Please post the CREATE TABLE definitions for your Cust and CAsh tables.

-PatP

6. Registered 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.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.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(CasinoWin) as CasinoWin,Sum(Bet) as Bet, Sum(EarnedPts) as EarnedPts,
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

8. Registered 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'.

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579

-PatP

10. Registered 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

11. Registered 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'.

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

13. Registered 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```

14. Registered User
Join Date
Mar 2012
Posts
27
Hi James,

Use cmktdb
SELECT
P.PlayerID,
P.Acct,
P.Title,
P.FirstName,
P.LastName,
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.CasinoWin,
A.Bet,
A.EarnedPts,
A.BonusPts,
A.RedeemPts,
A.NetPts,
A.EarnedComp,
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(CasinoWin) AS CasinoWin,
Sum(Bet) AS Bet,
Sum(EarnedPts) AS EarnedPts,
Sum(BonusPts) AS BonusPts,
Sum(RedeemPts) AS RedeemPts,
Sum(NetPts) AS NetPts,
Sum(EarnedComp) AS EarnedComp,
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. Registered 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'.

#### Posting Permissions

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