CODEWITHSUNDEEP
mysqlsqldatabasejoininsert
Geoff_S
Geoff_S

Reputation: 5105

Joining and selecting multiple tables and creating new column names

I have very limited experience with MySQL past standard queries, but when it comes to joins and relations between multiple tables I have a bit of an issue.

I've been tasked with creating a job that will pull a few values from a mysql database every 15 minutes but the info it needs to display is pulled from multiple tables.

I have worked with it for a while to figure out the relationships between everything for the phone system and I have discovered how I need to pull everything out but I'm trying to find the right way to create the job to do the joins.

I'm thinking of creating a new table for the info I need, with columns named as:

Extension | Total Talk Time | Total Calls | Outbound Calls | Inbound Calls | Missed Calls

I know that I need to start with the extension ID from my 'user' table and match it with 'extensionID' in my 'callSession'. There may be multiple instances of each extensionID but each instance creates a new 'UniqueCallID'.

The 'UniqueCallID' field then matches to 'UniqueCallID' in my 'CallSum' table. At that point, I just need to be able to say "For each 'uniqueCallID' that is associated with the same 'extensionID', get the sum of all instances in each column or a count of those instances".

Here is an example of what I need it to do:

callSession Table

UniqueCallID | extensionID |
----------------------------
A              123
B              123
C              123

callSum table

UniqueCallID | Duration | Answered | 
------------------------------------
A             10         1
B             5          1
C             15         0

newReport table

Extension | Total Talk Time | Total Calls | Missed Calls
--------------------------------------------------------
123         30                3             1

Hopefully that conveys my idea properly.

If I create a table to hold these values, I need to know how I would select, join and insert those things based on that diagram but I'm unable to construct the right query/statement.

Upvotes: 1

Views: 533

Answers (3)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

You simply JOIN the two tables, and do a group by on the extensionID. Also, add formulas to summarize and gather the info.

SELECT 
    `extensionID` AS `Extension`,
    SUM(`Duration`) AS `Total Talk Time`,
    COUNT(DISTINCT `UniqueCallID`) as `Total Calls`,
    SUM(IF(`Answered` = 1,0,1)) AS `Missed Calls`
FROM `callSession` a
JOIN `callSum` b
    ON a.`UniqueCallID` = b.`UniqueCallID`
GROUP BY a.`extensionID`
ORDER BY a.`extensionID`

Upvotes: 1

J. D.
J. D.

Reputation: 1571

This should do the trick. What you are being asked to do is to aggregate the number of and duration of calls. Unless explicitly requested, you do not need to create a new table to do this. The right combination of JOINs and AGGREGATEs will get the information you need. This should be pretty straightforward... the only semi-interesting part is calculating the number of missed calls, which is accomplished here using a "CASE" statement as a conditional check on whether each call was answered or not.

Pardon my syntax... My experience is with SQL Server.

SELECT CS.Extension, SUM(CA.Duration) [Total Talk Time], COUNT(CS.UniqueCallID) [Total Calls], SUM(CASE CS.Answered WHEN '0' THEN SELECT 1 ELSE SELECT 0 END CASE) [Missed Calls]
FROM callSession CS
INNER JOIN callSum CA ON CA.UniqueCallID = CS.UniqueCallID
GROUP BY CS.Extension

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You can use a join and group by

  select 
      a.extensionID
      ,  sum(b.Duration) as Total_Talk_Time 
      , count(b.Answered) as Total_Calls
      , count(b.Answered) -sum(b.Answered) as Missed_calls
  from callSession as a 
  inner join callSum as b on a.UniqueCallID = b.UniqueCallID
  group by a.extensionID

Upvotes: 1

Related Questions