Problems with Sql query join

Question

I am struggling with a sql query. I want to include the sum from an other table.

    SELECT DISTINCT 
    tblProject.CompanyID, 
    tblCompany.Name, 
    tblCompany.AvtalsKund, 
    tblProject.ProjectName, 
    tblProject.Estimate, 
    tblProject.ProjectStart, 
    tblProject.Deadline, 
    CONVERT(VARCHAR(8), tblProject.Deadline, 2) AS [YY.MM.DD] , 
    tblProject.PreOffered, 
    tblProject.ProjectType, 
    tblProjectType.ProjType, 
    tblOrdered.FirstName + + tblOrdered.LastName as OrderedFullName, 
    tblProject.ProjectID, 
    tblProject.RegDate, 
    tblProject.ProjectNr, 
    tblProject.ProjectNr 
    FROM tblProject 

    INNER JOIN tblCompany ON tblProject.CompanyID = tblCompany.CompanyID 
    ---> INNER JOIN (SELECT tblTimeRecord.ProjectID, SUM(CONVERT(float,replace([Hours],',','') )) 
FROM tblTimeRecord group by tblTimeRecord.ProjectID) as b
    ON b.ProjectID =  tblProject.ProjectID
    INNER JOIN tblTimeRecord ON tblTimeRecord.ProjectID = tblProject.ProjectID
    INNER JOIN tblProjectType ON tblProject.ProjectType = tblProjectType.ProjTypeID 
    LEFT OUTER JOIN tblOrdered ON tblProject.OrderedBy = tblOrdered.OrderedID 
    LEFT OUTER JOIN tblRel_WorkerProject ON tblProject.ProjectID = tblRel_WorkerProject.ProjectID 
    LEFT OUTER JOIN tblPerson ON tblPerson.PersonID = tblRel_WorkerProject.WorkerID 
    LEFT OUTER JOIN tblRel_StatusWorkerProject ON tblProject.ProjectID = tblRel_StatusWorkerProject.ProjectID

I want to include this sum-block from table tblTimeRecord.

I get a sum of timerapports with this code

    SELECT tblTimeRecord.ProjectID, 
SUM(CONVERT(float,replace([Hours],',','') )) 
FROM tblTimeRecord where ProjectID=1312 group by tblTimeRecord.ProjectID

Guess i do it in join?

Got it working with this.

 SELECT DISTINCT
tblProject.ProjectID,
Summa, 
tblProject.CompanyID, 
tblCompany.Name, 
tblCompany.AvtalsKund, 
tblProject.ProjectName, 
tblProject.Estimate, 
tblProject.ProjectStart, 
tblProject.Deadline, 
CONVERT(VARCHAR(8), tblProject.Deadline, 2) AS [YY.MM.DD] , 
tblProject.PreOffered, 
tblProject.ProjectType, 
tblProjectType.ProjType, 
tblOrdered.FirstName + + tblOrdered.LastName as OrderedFullName, 
tblProject.ProjectID, 
tblProject.RegDate, 
tblProject.ProjectNr, 
tblProject.ProjectNr 
FROM tblProject 
INNER JOIN tblCompany ON tblProject.CompanyID = tblCompany.CompanyID 
INNER JOIN (SELECT tblTimeRecord.ProjectID, SUM(CONVERT(float,replace([Hours],',','') )) as Summa FROM tblTimeRecord group by tblTimeRecord.ProjectID) as b
ON b.ProjectID =  tblProject.ProjectID
INNER JOIN tblTimeRecord ON tblTimeRecord.ProjectID = tblProject.ProjectID
INNER JOIN tblProjectType ON tblProject.ProjectType = tblProjectType.ProjTypeID 
LEFT OUTER JOIN tblOrdered ON tblProject.OrderedBy = tblOrdered.OrderedID 
LEFT OUTER JOIN tblRel_WorkerProject ON tblProject.ProjectID = tblRel_WorkerProject.ProjectID 
LEFT OUTER JOIN tblPerson ON tblPerson.PersonID = tblRel_WorkerProject.WorkerID 
LEFT OUTER JOIN tblRel_StatusWorkerProject ON tblProject.ProjectID = tblRel_StatusWorkerProject.ProjectID

To use aggregate functions (e.g. SUM), you need a GROUP BY clause. — jarlh
– jarlh, Commented May 13, 2015 at 11:19
Either you add the GROUP BY to your huge SELECT, or use a correlated sub-query to count. — jarlh
– jarlh, Commented May 13, 2015 at 11:22

Simon Darlow · Accepted Answer · 2015-05-13 11:24:49Z

1

There are two ways to do this.

You can use a WITH clause to create the aggregate table then join this to the main query.

Or do it this way:

SELECT m.BLAH
       ,m.FOO
       ,x.AMOUNT
FROM   MAINTABLE m
     LEFT JOIN
             (
                SELECT FOO
                       ,SUM(AMOUNT) as AMOUNT
                FROM   OTHERTABLE
                GROUP BY FOO
             ) x
             ON m.FOO = x.FOO

I prefer the second way.

answered May 13, 2015 at 11:24

Simon Darlow

5505 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problems with Sql query join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related