SQL query help :)

Question

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

Answer 1

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!

Answer 2

0

Hi John,

Yes thats correct

answered May 31 '12 at 12:55 PM

Unknownuser
0 ● 1 ● 1 ● 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 '12 at 12:59 PM ThomasRushton ♦

(comments are locked)

Answer 3

0

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

answered May 31 '12 at 02:39 PM

Unknownuser
0 ● 1 ● 1 ● 2

Can you please post the query as you currently have it?

May 31 '12 at 06:01 PM JohnM

(comments are locked)

SQL query help :)

Follow this question

Related Questions