CODEWITHSUNDEEP
sqlsql-server
mitomed
mitomed

Reputation: 2066

SQL Get aggregate as 0 for non existing row using inner joins

I am using SQL Server to query these three tables that look like (there are some extra columns but not that relevant):

And I would like to get the total sales per week and customer (showing at the same time the address details). I have come up with this query

SELECT a.Name, b.Street, b.StreetNo, c.Week, SUM (c.Total) as Total
FROM Customers a
    INNER JOIN Addresses b ON a.Id = b.CustomerId
    INNER JOIN Sales c ON b.Id = c.AddressId
GROUP BY a.Name, c.Week, b.Street, b.StreetNo

and even if my SQL skill are close to none it looks like it's doing its job. But now I would like to be able to show 0 whenever the one customer don't have sales for a particular week (weeks are just integers). And I wonder if somehow I should get distinct values of the weeks in the Sales table, and then loop through them (not sure how)

Any help?

Thanks

Upvotes: 2

Views: 221

Answers (3)

toonice
toonice

Reputation: 2236

Please try the following...

SELECT Name,
       Street,
       StreetNo,
       Week,
       SUM( CASE
                WHEN Total IS NULL THEN
                    0
                ELSE
                    Total
            END ) AS Total
FROM Customers a
JOIN Addresses b ON a.Id = b.CustomerId
RIGHT JOIN Sales c ON b.Id = c.AddressId
GROUP BY a.Name,
         c.Week,
         b.Street,
         b.StreetNo;

I have modified your statement in three places. The first is I changed your join to Sales to a RIGHT JOIN. This will join as it would with an INNER JOIN, but it will also keep the records from the table on the right side of the JOIN that do not have a matching record or group of records on the left, placing NULL values in the resulting dataset's fields that would have come from the left of the JOIN. A LEFT JOIN works in the same way, but with any extra records in the table on the left being retained.

I have removed the word INNER from your surviving INNER JOIN. Where JOIN is not preceded by a join type, an INNER JOIN is performed. Both JOIN and INNER JOIN are considered correct, but the prevailing protocol seems to be to leave the INNER out, where the RDBMS allows it to be left out (which SQL-Server does). Which you go with is still entirely up to you - I have left it out here for illustrative purposes.

The third change is that I have added a CASE statement that tests to see if the Total field contains a NULL value, which it will if there were no sales for that Customer for that Week. If it does then SUM() would return a NULL, so the CASE statement returns a 0 instead. If Total does not contain a NULL value, then the SUM() of all values of Total for that grouping is performed.

Please note that I am assuming that Total will not have any NULL values other than from the RIGHT JOIN. Please advise me if this assumption is incorrect.

Please also note that I have assumed that either there will be no missing Weeks for a Customer in the Sales table or that you are not interested in listing them if there are. Again, please advise me if this assumption is incorrect.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Use CROSS JOIN to generate the rows for all customers and weeks. Then use LEFT JOIN to bring in the data that is available:

SELECT c.Name, a.Street, a.StreetNo, w.Week,
       COALESCE(SUM(s.Total), 0) as Total
FROM Customers c CROSS JOIN
     (SELECT DISTINCT s.Week FROM sales s) w LEFT JOIN
     Addresses a
     ON c.CustomerId = a.CustomerId LEFT JOIN
     Sales s
     ON s.week = w.week AND s.AddressId = a.AddressId
GROUP BY c.Name, a.Street, a.StreetNo, w.Week;

Using table aliases is good, but the aliases should be abbreviations for the table names. So, a for Addresses not Customers.

Upvotes: 2

Serge
Serge

Reputation: 4036

You should generate a week numbers, rather than using DISTINCT. This is better in terms of performance and reliability. Then use a LEFT JOIN on the Sales table instead of an INNER JOIN:

SELECT   a.Name
        ,b.Street
        ,b.StreetNo
        ,weeks.[Week]
        ,COALESCE(SUM(c.Total),0) as Total
FROM Customers a
    INNER JOIN Addresses b ON a.Id = b.CustomerId
    CROSS JOIN (
        -- Generate a sequence of 52 integers (13 x 4)
        SELECT ROW_NUMBER() OVER (ORDER BY a.x) AS [Week]
        FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(x)
        CROSS JOIN (SELECT x FROM (VALUES(1),(1),(1),(1)) b(x)) b
    ) weeks
    LEFT JOIN Sales c ON b.Id = c.AddressId AND c.[Week] = weeek.[Week]
GROUP BY a.Name
        ,b.Street
        ,b.StreetNo
        ,weeks.[Week]

Upvotes: 1

Related Questions