CODEWITHSUNDEEP
sql
Vexxums
Vexxums

Reputation: 47

How To Display Customers Even If They Have No Sales (results)

I have a code that pulls all of a Reps new customers and their year to date sales. The only problem is it only pulls customers that have sales in the invdate range, but I need it to show all of the accounts with a 0 if they do not have any sales. Is there any way to achieve this? I tried using COALESCE and it didn't seem to work. I also tried using left, right, full outer joins. Any help would be appreciated!

select 
a.Acctnum,  
sum(a.invtotal) as total 
from invoices a right join accounts b on a.acctnum = b.acctnum where 
a.invdate between '1/1/2017' and '12/31/2017'
and a.sls = '78'
and b.sls = '78'
and b.activetype = 'y' and b.startdate > (getdate()-365)
group by a.acctnum
order by total desc

Upvotes: 0

Views: 71

Answers (2)

JNevill
JNevill

Reputation: 50248

You are restricting your results in your WHERE clause AFTER you join your table causing records to drop. Instead, switch to a LEFT OUTER JOIN with your accounts table driving the query. Then restrict your invoices table in your ON clause so that invoices are dropped BEFORE you join. 

SELECT a.Acctnum,
    sum(a.invtotal) AS total
FROM accounts b
    LEFT OUTER JOIN invoices a ON
        a.accntnum = b.acctnum AND
        --Put the restrictions on your left most table here
        --so they are removed BEFORE joining. 
         a.invdate BETWEEN '1/1/2017' AND '12/31/2017'
        AND a.sls = '78'
WHERE
    b.sls = '78'
    AND b.activetype = 'y'
    AND b.startdate > (getdate() - 365)
GROUP BY a.acctnum
ORDER BY total DESC

It's a bit like doing a subquery in invoices before joining in as the left table. It's just easier to drop the conditions into the ON clause.

Upvotes: 2

HLGEM
HLGEM

Reputation: 96630

Your problem is you where clauses are changing the right join to an inner join. Put all the ones that are aliased by a. into the ON clause.

Upvotes: 0

Related Questions