Mysql – Query to display 3 metrics with corresponding date

MySQL

I need some help writing a query
Let me first explain what I need to achieve. So we have 3 tables in our database, one that records quotes, another sales and lastly welcome calls. Each of these 3 metrics can occur on a different date or sometimes the same date. I need to somehow query these 3 tables to return the following result. All 3 tables contain the lead_id, date and if quote/sale/welcome calls etc done. Using MySQL

enter image description here

Best Answer

Assuming that (date, lead_id) is defined as UNIQUE in each table you may use

SELECT date, lead_in, SUM(quote), SUM(sale), SUM(welcome)
FROM ( SELECT date, lead_in, quote, 0 AS sale, 0 AS welcome FROM quotes
       UNION ALL
       SELECT date, lead_in, 0, sale, 0 FROM sales
       UNION ALL
       SELECT date, lead_in, 0, 0, welcome FROM welcomes ) totally
GROUP BY date, lead_in;
Related Question