Combine multiple SQL select statements into columns

Question

I'm having a hard time wrapping my mind around this, any assistance is most appreciated.

I have two select statements with joins to 1 or more tables.

SELECT repinfo.repName, SUM(callstatssummary.CallsIn)
FROM repinfo
LEFT JOIN callstatssummary
ON repinfo.isaacID = callstatssummary.IsaacID AND callstatssummary.ShiftDate >= '2013-02-10' AND callstatssummary.ShiftDate <= '2013-02-16'
GROUP BY repinfo.repName;

The output of the first statement is a list of everyone in the repinfo table, with the sum of the total calls they took during the week. I used a left join to include people who didn't take calls in the result.

SELECT repinfo.repName, SUM(`1036`.afterRgu) - SUM(`1036`.priorRgu)
FROM repinfo
JOIN reporders
ON repinfo.repID = reporders.oRep
JOIN `1036`
ON reporders.workOrder = `1036`.workOrder AND `1036`.entryDate >= '2013-02-10' AND `1036`.entryDate <= '2013-02-16' AND `1036`.afterRgu >= `1036`.priorRgu
GROUP BY repinfo.repName;

The second statement outputs the number of products that each person sold during the week. The repinfo table has the information about the representative, which joins with the reporders table to match the work order. The 1036 table has detailed information about the orders.

I am looking to output something like this - essentially combine the output of the two select statements:

|  repName  |  SUM(callstatssummary.CallsIn) |  SUM(`1036`.afterRgu) - SUM(`1036`.priorRgu)  |
______________________________________________________________________________________________
| Bruce W   | 41                             | 13                                            |
| Cathy M   | 84                             | 17                                            |
| Jonah S   | NULL                           | 29                                            |

Any suggestions?

Answer 1

One way to combine those statements is to make each of them a derived-table / inline-view and join on repName.

Please note: Obviously you would want to join on a rep ID number (or whatever you call the primary key of the repinfo table) if two reps can have the same name.

select 
  r.repName, c.sumCallsIn, o.sumProdSold
from
  repinfo r
  left join (
    SELECT repinfo.repName, 
           SUM(callstatssummary.CallsIn) sumCallsIn
    FROM   repinfo
           LEFT JOIN callstatssummary
           ON repinfo.isaacID = callstatssummary.IsaacID 
           AND callstatssummary.ShiftDate >= '2013-02-10' 
           AND callstatssummary.ShiftDate <= '2013-02-16'
    GROUP BY repinfo.repName
  ) c
  on c.repName = r.repName
  left join (
    SELECT repinfo.repName, 
           SUM(`1036`.afterRgu) - SUM(`1036`.priorRgu) sumProdSold
    FROM   repinfo
           JOIN reporders
           ON repinfo.repID = reporders.oRep
           JOIN `1036`
           ON reporders.workOrder = `1036`.workOrder 
           AND `1036`.entryDate >= '2013-02-10' 
           AND `1036`.entryDate <= '2013-02-16' 
           AND `1036`.afterRgu >= `1036`.priorRgu
    GROUP BY repinfo.repName
  ) o
  on r.repName = o.repName
order by r.repName;