How to find the percentage of the number of a certain value in my SQL?-CodePudding

I have a data in MySQL that looks like this. Let's call it "calls" table

call_no	call_date	caller	call_time
1	2022-06-01	Mark	11:00
2	2022-06-01	John	11:30
3	2022-06-01	Alice	1:15
4	2022-06-01	Mark	1:30
5	2022-06-01	Susan	4:00
6	2022-06-02	Mark	9:20
7	2022-06-02	Alice	11:15
8	2022-06-02	Mark	11:30
9	2022-06-02	Mark	2:30
10	2022-06-03	Alice	9:00
11	2022-06-03	Susan	10:30
12	2022-06-03	John	1:00
13	2022-06-03	Mark	1:30
14	2022-06-03	John	2:30

...

I wanted to know the proportion of Mark's calls to the total number of calls every single day on June 1, June 2 and June 3 in terms of percentage.

I am using the following script but it doesn't work:

SELECT call_date
     , (SELECT COUNT(call_no) 
        FROM calls 
        WHERE caller='Mark') / COUNT(call_no)*100 AS calls_percentage
FROM calls
GROUP BY call_date

There result I am expecting is 40% on June 1st, 75% on June 2 and 20% on June 3.

CodePudding user response：

If you want to have caller percentage of all, use this:

SELECT 
  c.`caller`,
  COUNT(*) AS totalCalls ,
  COUNT(*)/(SELECT COUNT(*) FROM call_records c1)*100
FROM
  `call_records` c 
GROUP BY c.`caller`

Or if you want specific caller just add a where clause; like

SELECT 
  c.`caller`,
  COUNT(*) AS totalCalls ,
  COUNT(*)/(SELECT COUNT(*) FROM call_records c1)*100
FROM
  `call_records` c 
  WHERE c.caller='Mark'
GROUP BY c.`caller`

EDIT I saw you want it date wise, try using:

SELECT 
  c.`caller`,
  COUNT(*) AS totalCalls ,
  c.call_date,
  (COUNT(*)/(SELECT COUNT(*) FROM call_records c1 WHERE c1.call_date=c.call_date)*100) AS callPercentage
FROM
  `call_records` c 
  WHERE c.caller='Mark'
GROUP BY c.`caller` , c.call_date

CodePudding user response：

You can do following:

SELECT call_date, 
SUM(caller = 'Mark') / COUNT(caller) * 100 AS percentage
FROM calls
GROUP BY call_date

To add another caller like John, you can do this:

SELECT call_date,
SUM(caller = 'Mark') / COUNT(caller) * 100 AS percentageMark,
SUM(caller = 'John') / COUNT(caller) * 100 AS percentageJohn
FROM calls
GROUP BY call_date

This will find out the percentage of John's and Mark's calls compared to all calls per day:

SELECT call_date,
SUM(caller IN ('Mark','John')) / COUNT(caller) * 100 AS percentageMarkAndJohn
FROM calls
GROUP BY call_date

CodePudding user response：

Supposing the call_time column is a time data type (with the value at hour position being 00 if not explicitly specified), if you would like to calculate the call duration percentage for Mark each day rather than call count percentage, try this.

select call_date,
    concat((select sum(time_to_sec(call_time)) from calls where caller='Mark' and call_date=c.call_date)/sum(time_to_sec(call_time)) * 100,'%') as duration_percentage
from calls c
group by call_date
;

Tested it on workbench and got the following result set:
call_date duration_percentage
2022-06-01 42.7350%
2022-06-02 67.4699%
2022-06-03 6.1224%