LEFT JOIN Producing Inflated COUNT() numbers

Question

I have 3 tables that I am using a Right Join on. The data is setup like below - and my issue with it is that numbers being returned are inaccurate, such that when I execute the query I get this returned (which is double and triple the accurate value)

vendor     TotalSales   TotalCases
Vendor 1    61.40          6

but if you manually do the math it should be

vendor     TotalSales   TotalCases
Vendor 1    30.70          2

What must I change in my query so that the above results are returned?

  Declare @BBC Table
  (
    vendor varchar(250)
    ,vendorcasenum varchar(100)
    ,casenumdate date
  )

  Declare @AllVendor Table
  (
    vendor varchar(250)
  )

  Declare @TotalSalesAmt Table
  (
    vendor varchar(250)
    ,saleamt decimal(10,2)
  )

  Insert Into @TotalSalesAmt (vendor, saleamt) VALUES
  ('Vendor 1', '10.20'), ('Vendor 2', '10.10'), ('Vendor 1', '.40')
  ,('Vendor 1', '20.10'), ('Vendor 2', '20.10'), ('Vendor 3', '20.00')

  Insert Into @AllVendor (vendor) Values
  ('Vendor 1'), ('Vendor 2'), ('Vendor 3'), ('Vendor 4')
  ,('Vendor 5'), ('Vendor 6'), ('Vendor 7'), ('Vendor 8')
  ,('Vendor 9'), ('Vendor 10'), ('Vendor 11'), ('Vendor 12')
  ,('Vendor 13'), ('Vendor 14'), ('Vendor 15'), ('Vendor 16')
  ,('Vendor 17'), ('Vendor 18'), ('Vendor 19'), ('Vendor 20')

 Insert Into @BBC (vendor, vendorcasenum, casenumdate) VALUES
('Vendor 11',   'A12344',    '2017-01-19')
,('Vendor 10',  'A12311',    '2014-05-12')
,('Vendor 9',   'A12889',    '2015-07-10')
,('Vendor 8',   'A12988',    '2016-07-01')
,('Vendor 7',   'A12931',    '2012-03-07')
,('Vendor 6',   'A12199',    '2011-10-05')
,('Vendor 5',   'E12331',    '2011-10-11')
,('Vendor 4',   'E12391',    '2014-12-16')
,('Vendor 3',   'E12300',    '2011-07-15')
,('Vendor 2',   'E11001',    '2011-06-15')
,('Vendor 1',   'E12301',    '2013-11-06')
,('Vendor 1',   'E12221',    '2013-11-06')

Select
av.vendor
,TotalSales = SUM(ISNULL(tsa.saleamt,0))
,TotalCases = COUNT(bbc.vendorcasenum)
FROM @AllVendor av
LEFT JOIN @BBC bbc
ON av.vendor = bbc.vendor
LEFT JOIN @TotalSalesAmt tsa
ON tsa.vendor = av.vendor
GROUP BY av.vendor
ORDER BY av.vendor ASC

EDIT
I also tried using a CTE to achieve my desired outcome, but came out with the same incorrect results:

WITH tsa As
(
    Select 
    vendor
    ,saleamt
    FROM @TotalSalesAmt
)
,BBC As
(
    Select 
    vendor
    ,vendorcasenum
    FROM @BBC
)
Select 
av.vendor
,TotalSales = ISNULL(SUM(tsa.saleamt),0)
,TotalCases = COUNT(bbc.vendorcasenum)
FROM @AllVendor av
LEFT JOIN TSA tsa
ON tsa.vendor = av.vendor
LEFT JOIN BBC bbc
ON bbc.vendor = av.vendor
GROUP BY av.vendor
ORDER BY av.vendor

Answer 1

If you run the query without aggregates, you'll see what is happening:

Select
    *
FROM @AllVendor av
    LEFT JOIN @BBC bbc
        ON av.vendor = bbc.vendor
    LEFT JOIN @TotalSalesAmt tsa
        ON tsa.vendor = av.vendor
where av.vendor = 'vendor 1'

+----------+----------+---------------+---------------------+----------+---------+
| vendor   |  vendor  | vendorcasenum |     casenumdate     |   vendor | saleamt |
+----------+----------+---------------+---------------------+----------+---------+
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 10,20   |
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 0,40    |
| Vendor 1 | Vendor 1 | E12301        | 06.11.2013 00:00:00 | Vendor 1 | 20,10   |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 10,20   |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 0,40    |
| Vendor 1 | Vendor 1 | E12221        | 06.11.2013 00:00:00 | Vendor 1 | 20,10   |
+----------+----------+---------------+---------------------+----------+---------+

Due there are 2 rows in @BBC table of Vendor 1:

('Vendor 1',   'E12301',    '2013-11-06')
('Vendor 1',   'E12221',    '2013-11-06')

The aggregated SUM(saleamt) = 61.40

You can use one subquery to calculate SUM(saleamt) and another to calculate COUNT(vendorcasenum):

Select
    vendor,
    (select ISNULL(SUM(tsa.saleamt),0)
     from @TotalSalesAmt tsa
     where tsa.vendor = av.vendor) TotalSales,
    (select COUNT(bbc.vendorcasenum)
     from @BBC bbc
     where av.vendor = bbc.vendor) TotalCases
FROM @AllVendor av
ORDER BY av.vendor ASC;

This is the final result:

+-----------+------------+------------+
|   vendor  | TotalSales | TotalCases |
+-----------+------------+------------+
| Vendor 1  | 30,70      | 2          |
| Vendor 10 | 0,00       | 1          |
| Vendor 11 | 0,00       | 1          |
| Vendor 12 | 0,00       | 0          |
| Vendor 13 | 0,00       | 0          |
| Vendor 14 | 0,00       | 0          |
| Vendor 15 | 0,00       | 0          |
| Vendor 16 | 0,00       | 0          |
| Vendor 17 | 0,00       | 0          |
| Vendor 18 | 0,00       | 0          |
| Vendor 19 | 0,00       | 0          |
| Vendor 2  | 30,20      | 1          |
| Vendor 20 | 0,00       | 0          |
| Vendor 3  | 20,00      | 1          |
| Vendor 4  | 0,00       | 1          |
| Vendor 5  | 0,00       | 1          |
| Vendor 6  | 0,00       | 1          |
| Vendor 7  | 0,00       | 1          |
| Vendor 8  | 0,00       | 1          |
| Vendor 9  | 0,00       | 1          |
+-----------+------------+------------+

Answer 2

There is also another method, without inline subqueries, where you GROUP BY first and then join, in 3 flavours:

• derived tables
• CTEs
• OUTER APPLY

Using CTEs:

WITH 
  tsa AS
  (
    SELECT
        vendor
        TotalSales = SUM(saleamt)
    FROM @TotalSalesAmt
    GROUP BY vendor
  ),
  bbc AS
  (
    SELECT  
        vendor,
        TotalCases = COUNT(vendorcasenum)
    FROM @BBC
    GROUP BY vendor
  )
SELECT 
    av.vendor,
    TotalSales = COALESCE(tsa.TotalSales, 0),
    TotalCases = COALESCE(bbc.TotalCases, 0)
FROM 
    @AllVendor AS av
  LEFT JOIN tsa
    ON tsa.vendor = av.vendor
  LEFT JOIN bbc
    ON bbc.vendor = av.vendor
ORDER BY
    av.vendor ;

Answer 3

As suggested by @ypercubeᵀᴹ - in his comments, an alternative solution would be to use OUTER APPLY -->

SELECT  
    av.vendor,  
    COALESCE(saleamt,0) AS TotalSales,
    COALESCE(TotalCases,0)  AS TotalCases
FROM 
    @AllVendor AS av
OUTER APPLY
    ( SELECT vendor
           , SUM(saleamt) AS saleamt
      FROM @TotalSalesAmt
      WHERE vendor = av.vendor  
      GROUP BY vendor
    ) AS b
OUTER APPLY
    ( SELECT vendor
           , COUNT(vendorcasenum) AS TotalCases
      FROM @BBC 
      WHERE vendor = av.vendor  
      GROUP BY vendor
    ) AS c ;