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?

mechanical_meat · Accepted Answer · 2013-02-22 23:20:34Z

4

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;

edited Feb 22, 2013 at 23:20

answered Feb 22, 2013 at 23:15

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen Cluff Over a year ago

Excellent response, thank you. I've had little experience with derived tables before, but was able to read through and understand perfectly. Works great!

Collectives™ on Stack Overflow

Combine multiple SQL select statements into columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related