views:

1050

answers:

5

Hi All

I have two tables, one with all my Branches, and one with all my sales. The sales table also contains a Sales Rep ID, a Branch ID, a month and a year.

I need a query that will return the sum of a specific rep's sales for a year, grouped by branch and month, and the query must return 0 if there has been no sales in a branch for that month. I have the following, which does not return 0 if there are no sales:

SELECT
    s.Month,
    b.BranchName,
    SUM(s.InvoiceAmount) AS 'Sales'
FROM 
    Branch b
INNER JOIN
    Sales s ON s.BranchID = b.BranchID
WHERE
    s.Year = 2008
AND
    s.SalesRepID= 11
GROUP BY 
    s.Month,
    b.BranchName
ORDER BY 
    s.Month,
    b.BranchName
+2  A: 

You'll need to do a LEFT JOIN to Sales, so as to return even the reps that do not have any records in the Sales table.

David Hedlund
Left join should add those branches with no sales appear in the final query and the aggregate will then return for them.
Spence
-1 There is a where clause on s.SalesRepId and so a left outer join onto Sales would be pointless.
pjp
A: 

If your query is returning NULL, you can use one of the coalescing methods: COALESCE(SUM(...), 0) will return the first non-NULL value in the list...

Arjan Einbu
Shouldn't the coalesce be inside the sum. Or the sum may sum over NULL values. I beleive that this raises a warning in SQL sever.
pjp
Warning: Null value is eliminated by an aggregate or other SET operation.(1 row(s) affected)
pjp
A: 

I changed the join from inner to left outer and added the ISNULL function for branches with no sales.

SELECT

    b.BranchName,
    s.Month,
    SUM(ISNULL(s.InvoiceAmount,0)) AS 'Sales'
FROM 
    Branch b
LEFT JOIN
    Sales s ON s.BranchID = b.BranchID
WHERE
    s.Year = 2008
AND
    s.SalesRepID= 11
GROUP BY 
    s.Month,
    b.BranchName
ORDER BY 
    s.Month,
    b.BranchName
gjutras
The question asks for sales by month even if there are no sales in a particular month. This query returns results for the whole year.
pjp
This did not change my result.
Ravish
A: 

You need to use a left join and an isnull to get the sum right:

SELECT    b.BranchName
,    SUM(isnull(s.InvoiceAmount, 0)) AS 'Sales'
FROM     Branch b
LEFT JOIN    Sales s ON s.BranchID = b.BranchID 
GROUP BY     s.Month,    b.BranchName
ORDER BY     s.Month,    b.BranchName

You still need to do some more work with it to get the months showing too if a salesman has no sales in a particular month.

mattmanser
+2  A: 

You will need to add the "missing" data to be able to join it.

SELECT
    b.BranchName,
    SUM(ISNULL(s.InvoiceAmount, 0)) AS 'Sales',
    s.Month
FROM 
    Branch b
    LEFT OUTER JOIN (
            SELECT
        b.BranchID AS BranchID
        , s.SalesRepID AS SalesRepID
        , Months.Month AS Month
        , Years.Year AS Year
        , 0 AS InvoiceAmount
      FROM 
          Sales s
          CROSS JOIN (
            SELECT 1 AS Month
            UNION ALL SELECT 2
            UNION ALL SELECT 3
            UNION ALL SELECT 4
            UNION ALL SELECT 5
            UNION ALL SELECT 6
            UNION ALL SELECT 7
            UNION ALL SELECT 8
            UNION ALL SELECT 9
            UNION ALL SELECT 10
            UNION ALL SELECT 11
            UNION ALL SELECT 12
            ) Months
          CROSS JOIN (
            SELECT 2007 AS Year
            UNION ALL SELECT 2008
            UNION ALL SELECT 2009
            ) Years
          CROSS JOIN Branch b
      UNION ALL SELECT 
        s.BranchID AS BranchID
        , s.SalesRepID AS SalesRepID
        , s.Month AS Month
        , s.Year AS Year
        , s.InvoiceAmount AS InvoiceAmount
      FROM Sales s      
    )s ON s.BranchID = b.BranchID    
WHERE
    s.Year = 2008
    AND s.SalesRepID= 11
GROUP BY 
    s.Month,
    b.BranchName
ORDER BY 
    b.BranchName,
    s.Month
Lieven
I was about to post something similar
pjp
glad to hear I am not alone in the way I interpreted the question. All other answers seem to focus on NULL values in InvoiceAmount. I doubt that is what the OP meant.
Lieven
Thanks Lieven, that works (sort of). I now get 0 values for months in which the rep did not have any sales in that branch, but the query as a whole only returns branches where the rep has had sales at some point in the year.
Ravish
I have adjusted the query to match your requirement
Lieven
Just did exactly the same thing, works a charm, thanks.
Ravish