Adding Column to MySQL table query

Question

I have a relatively complex query with some sub-queries that I'm using to create a "Leaderboard" for my sales reps. Problem is that the primary data is filtered via Sale_Date, while I need one of the columns to be filtered by Install_Date (only the Installs column). I'm using MySQL workbench and a 3rd party business intelligence platform to output the data in a presentable manner. Below is a simplified version of what I'm currently doing for the, but it obviously doesn't work or doesn't give me what I want.

select `Rep`, `Sales`, `Passed Credit`, `Sales Scheduled`, Installs
from 
    (select Rep_Name as `Rep`, count(*) as `Sales`..., count(...) as `Sales Scheduled`
        from ss.customers
            left join ss.users
                on customers.rep_id = users.id
        where Sale_Date between curdate() - interval 6 day and curdate()
        group by Rep_Name with rollup) cust,
    (select Rep_Name, count(*) as `Installs`
        from ss.customers
            left join ss.installs
                on customers.id = installs.id
            left join ss.users
                on customers.rep_id = users.id
        where Install_Date between curdate() - interval 6 day and curdate()
        group by Rep_Name) inst
order by `Sales` desc, `Passed Credit` desc, ..., `Installs` desc

The output I get is correct for all of the fields in the first table "cust" but all of my reps show the first "Installs" count returned during the second half of the query whether its 1 or 20.

It is returning something like this:

Rep ----- Sales ----- Passed Credit ----- Sales Scheduled ----- Installs
John  |    10     |         7         |         6           |      5
Mike  |    8      |         7         |         6           |      5
Trey  |    8      |         6         |         4           |      5
Drew  |    6      |         3         |         3           |      5
Angel |    3      |         2         |         4           |      5
Total |    35     |         25        |         23          |      22

The Install table does also include some people who have no sales this week, but they are also not showing up here, which they should be. Ideally this is what the table would look like:

Rep ----- Sales ----- Passed Credit ----- Sales Scheduled ----- Installs
John  |    10     |         7         |         6           |      5
Mike  |    8      |         7         |         6           |      9
Trey  |    8      |         6         |         4           |      4
Drew  |    6      |         3         |         3           |      4
Angel |    3      |         2         |         4           |      0
Tracy |    0      |         0         |         0           |      3
Diego |    0      |         0         |         0           |      1
Total |    35     |         25        |         23          |      26

I've tried some crazy combinations of joins, unions, and where statements. Any other ideas on how to obtain this? Any help is appreciated!

Update 1 Thanks to spencer7593 I was able to solve my problem of the "Installs" column showing incorrectly by using a left join. Currently still working on displaying reps with 0 sales for the week, but have installs (from sales in previous weeks).

Necessary qualifying fields for the tables listed that I forgot to include before:

Customers - rep_id, Sale_Date, id, Passed_Credit, Sale_Scheduled
Users - id (which is customers.rep_id), Rep_Name, Rep_Office_Location
Installs - id (which is customers.id), Install_Date, Install_Status

I know our CRM database is a mess with multiple columns of the same name on different tables representing different things. I didn't design it and it's what I have to live with for the mean time.

Answer 1

Your query is producing a Cartesian product of the two inline views, cust and inst.

I suspect you want to "match" those on Rep_Name. Replace that comma join operator with a LEFT JOIN keyword, and add an ON clause to match cust.Rep with inst.Rep_Name. Since you are returning the WITH ROLLUP, maybe you want to match that with a total row from inst?

Most of the column references are unqualified, so we can't tell which table contains which column. (Are Rep_Name and Sale_Date coming from ss.customers or ss.users?) I recommend you qualify all column references, as an aid to the future reader, and to avoid "ambiguous column" errors cropping up later when columns are added to the tables.

If you want to return all Rep_Name, including those with no Sales, then you may need to move the condition in the WHERE clause to the ON clause (if Sale_Date is a reference to a column in the users table.)

Making some guesses about which table contains which columns, and assuming that Rep_Name is not null, something like this:

SELECT IFNULL(cust.`Rep_Name`,'Total') AS `Rep`
     , cust.`Sales`
     , cust.`Passed Credit`
     , cust.`Sales Scheduled`
     , inst.`Installs`
  FROM ( SELECT cc.`Rep_Name`   AS `Rep_Name`
              , COUNT(cu.id)    AS `Sales`
              , ...
              , COUNT(...)      AS `Sales Scheduled`
           FROM ss.customers cc
           LEFT
           JOIN ss.users cu
             ON cu.`id` = cc.`rep_id`
            AND cu.`Sale_Date` BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
          GROUP BY cc.`Rep_Name`
           WITH ROLLUP
        ) cust
  LEFT
  JOIN ( SELECT ic.`Rep_Name`   AS `Rep_Name`
              , COUNT(ii.id)    AS `Installs`
           FROM ss.customers ic
           LEFT
           JOIN ss.installs ii
             ON ii.`id` = ic.`id`
            AND ii.`Install_Date` BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
          GROUP BY ic.`Rep_Name`
           WITH ROLLUP
        ) inst
     ON inst.`Rep_Name` <=> cust.`Rep_Name`
  ORDER
     BY cust.`Rep_Name` IS NOT NULL DESC
      , cust.`Sales` DESC
      , cust.`Passed Credit` DESC
      , ...
      , inst.`Installs` DESC

NOTE: any rows with a NULL value of Rep_Name would "match" the WITH ROLLUP total row. It looks like you want the total row to be last, so we can add an expression to the ORDER BY to have that row be last.

---

FOLLOWUP:

To get "zero" counts for sales, make the driving table give you a list of all Rep_Name, and do an outer join to the table that gives you the sales.

As a demonstration, using different table names

Get a list of all sales reps

 SELECT r.rep_id, r.rep_name
   FROM all_reps r
  ORDER BY r.id

To get the list of all sales reps along with any matching "sales", we can use an outer join. Note that the conditions for the matching are in the ON clause of the outer join, not the WHERE clause.

 SELECT r.rep_id
      , r.rep_name
      , s.sale_date
   FROM all_reps r
   LEFT
   JOIN sales s
     ON s.rep_id = r.rep_id
    AND s.sale_date >= TRUNC(NOW()) - INTERVAL 6 DAY
  ORDER BY r.rep_id, s.sale_date

For reps that don't have any "matching" row in sales, we'll get back a row with the columns from r. And on those rows, the columns from s will be NULL.

Note that if we moved the predicate (condition) on s.sale_date to the WHERE clause, that would filter out all of the rows that had a NULL value for s.sale_date. And that would negate the "outerness" of the join and make it equivalent to an inner join.