x

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
more ▼

asked May 30, 2012 at 03:10 PM in Default

Unknownuser gravatar image

Unknownuser
0 2 2 2

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
May 30, 2012 at 04:32 PM Unknownuser

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!
May 30, 2012 at 06:30 PM JohnM

Hi,

It exists in dbo.Viewaccum1day(TheorWin) but I just want to sum up all the values per playerid and location as ClubADW..
May 31, 2012 at 08:35 AM Unknownuser

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
May 31, 2012 at 08:36 AM Unknownuser

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.
May 31, 2012 at 01:10 PM JohnM
show all comments (comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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!
more ▼

answered May 30, 2012 at 03:19 PM

JohnM gravatar image

JohnM
6.9k 1 3 7

Hi There,

Thanks for the reply. Great thanks so much for the pointer. I will give it a go asap..

:)
May 30, 2012 at 03:35 PM Unknownuser
(comments are locked)
10|1200 characters needed characters left

Hi John,

Yes thats correct
more ▼

answered May 31, 2012 at 12:55 PM

Unknownuser gravatar image

Unknownuser
0 2 2 2

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!
May 31, 2012 at 12:59 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

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'.
more ▼

answered May 31, 2012 at 02:39 PM

Unknownuser gravatar image

Unknownuser
0 2 2 2

Can you please post the query as you currently have it?
May 31, 2012 at 06:01 PM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x118
x40
x37
x24

asked: May 30, 2012 at 03:10 PM

Seen: 1209 times

Last Updated: May 31, 2012 at 06:01 PM