LEFT JOIN Producing Inflated COUNT() – SQL Server Fix

sql serversql-server-2008-r2t-sql

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

Best Answer

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          |
+-----------+------------+------------+
Related Question