I currently have two tables that I am trying to pull data from, one being Customers and one being Bills.
Customers is a static table that just lists all the customers, but the reason I need this table is because it provides the actual human-friendly customer name instead of the less helpful numeric customer ID.
Bills is a frequently updated table and I want to count the number of updates per day for each customer. So I came up with the following:
SELECT c.name, COUNT(DISTINCT b.billid) from customers c JOIN bills b ON b.customerid = c.customerid WHERE b.createdate = CURDATE() -INTERVAL 1 DAY GROUP BY b.customerid;
This works wonderfully with one problem: There are some clients that don't process on a given day. And while this is currently a small select overall (I get about 24 out of a max of 31 rows back) this data is going to be used in metrics reports and transposing the data with these missing rows could eventually become a problem.
What I want is to be able to get EVERY name from Customers showing up in the first column, and then if there is no data from Bills for that customer that day, I'd like it to show up as 0 or NULL. I've been banging my head against the wall for a while now, and I'm not sure I can actually do this or not in MySQL.