Query sum LEFT JOIN not returning data

Question

I'm trying to creating a query where I return a property name and a total of work orders opened for each property. However, I'm getting always null for the TotalWorkOrders even though I have work orders for the properties.

My query:

SELECT p.PropertyName, p.PropertyID,
   TotalWorkOrders
  FROM Properties p
  LEFT JOIN (
        Select
          PropertyID,
          SUM(1) AS TotalWorkOrders
        From WorkOrders
       Where WorkOrderComplete=1
       ) sums ON sums.PropertyID = p.PropertyID
 Where p.PropertyID in (Select PropertyID From Properties Where CompanyID = 290)

Does anybody know what I'm doing wrong?

Thanks

No GROUP BY in the subquery?

jarlh
– jarlh

2018-02-08 15:59:02 +00:00
Commented Feb 8, 2018 at 15:59 — jarlh
– jarlh, Commented Feb 8, 2018 at 15:59
Thanks... A group by fixed the problem

Max Boy
– Max Boy

2018-02-08 15:59:37 +00:00
Commented Feb 8, 2018 at 15:59 — Max Boy
– Max Boy, Commented Feb 8, 2018 at 15:59

SuperSql · Accepted Answer · 2018-02-08 16:36:29Z

1

You should probably add a GROUP BY inside the "sums" sub-query.

answered Feb 8, 2018 at 16:36

SuperSql

897 bronze badges

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

Collectives™ on Stack Overflow

Query sum LEFT JOIN not returning data

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