T-SQL Query not returning proper results

Question

I have a query that I want to capture Sales for Parts. I am expecting to get the full results from the Parts table and if there are no Sales for that Part in the timeframe, I want to see a 0 in the Sales column. I am not seeing that. I am just getting the Parts that had Sales.

SELECT
    Part,
    Sum(Sales)
FROM
    dbo.Parts
    LEFT OUTER JOIN
    dbo.SalesData ON Part = Part
WHERE
    SalesDate > '2011-12-31'
GROUP BY
    Part
ORDER BY
    Part

What am I doing wrong?

Unless Part is in only one table then it is ambiguous. If part is only in one table then that join is a cross join. — paparazzo, Jul 17 '13 at 19:14
I understand that. I was just simplifying the code to get at the hear of the problem. — tsqln00b, Jul 17 '13 at 19:23

score 1 · Accepted Answer · answered Jul 17 '13 at 16:49

1

I believe this is because your WHERE clause is removing all the parts that don't have sales because they won't have a SalesDate.

Try:-

SELECT
    Part,
    Sum(Sales)
FROM
    dbo.Parts
    LEFT OUTER JOIN
    dbo.SalesData ON Part = Part
    AND SalesDate > '2011-12-31'
GROUP BY
    Part
ORDER BY
    Part

answered Jul 17 '13 at 16:49

baynezy

6,493
10
48
73

He's right: You could use `AND COALESCE(salesdate, CURRENT_TIMESTAMP) > '2011-12-31'`, which would make the output display sales for every part in the catalog, whether there were actual sales or not. – Curt Jul 17 '13 at 16:53
Yes. The main issue was the WHERE condition. I did not provide the exacting table field names as I don't want to give anything away in case my company gets pissed at me. – tsqln00b Jul 17 '13 at 19:04

T-SQL Query not returning proper results

1 Answers1