Rows with Revenue =0, how do I show the 0?

Question

The following code shows all our customers that have made purchases since our database was created and how much money they have spent. There are a couple customers that have not made purchases since we implemented the new database but they are not appearing when I run this code. I have looked around this site for similar examples but the solutions are too complex for me. There is also a customers table that shows the results for all of our 'n' customers, that table connects to the orders table through customerID. Not sure if that will help.

select t3.CustomerID, sum(Revenue) as Revenue
from
(
select orderid, sum(UnitPrice*quantity) as Revenue from [Order Details]
group by OrderID
)t1
inner join
(
select customerid,orderid from orders
)t3
on t1.orderid=t3.orderid
group by t3.CustomerID

You want an `OUTER JOIN` instead of `INNER JOIN`. See http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join — ThisSuitIsBlackNot, Jan 28 '14 at 19:47

Gordon Linoff · Answer 1 · 2014-01-28T20:15:58.207

1

I think you just want this rather simpler query:

  select c.CustomerID, sum(od.UnitPrice * od.quantity) as Revenue
  from customers c left outer join
       orders o
       on o.CustomerId = c.CustomerId left outer join
       `Order Details` od 
       on od.OrderId = o.OrderId
  group by c.CustomerID;

edited Jan 28 '14 at 20:15

answered Jan 28 '14 at 19:47

Gordon Linoff

1,242,037
58
646
786

Exactly, I added a [SQLFiddle here](http://sqlfiddle.com/#!2/fc941/3/0) if you're interested in playing around with this query. – Simon Jan 28 '14 at 19:52
1

@SimonKrenger . . . Here is a slightly revised SQL Fiddle (http://sqlfiddle.com/#!2/976e4/2) that includes the unpleasant table name from the OP (`Order Details` instead of `order_details`). – Gordon Linoff Jan 28 '14 at 20:17

Rows with Revenue =0, how do I show the 0?

1 Answers1