Geek asks and answers

MYSQL selects the average number of records

I have a table that has a unique key every time the user creates a case:

id|doctor_id|created_dt
--|---------|-----------
 1|23       |datetimestamp
 2|23       |datetimestamp
 3|17       |datetimestamp

How can I select and return the average number of records a user has per month?

I tried this:

SELECT avg (id)
FROM `cases`
WHERE created_dt BETWEEN DATE_SUB(CURDATE(),INTERVAL 90 DAY) AND CURDATE() 
and doctor_id = 17

But this returns a ridiculously large meaning that cannot be true.

To clarify: I'm trying to get something like a doctor id 17 has an average of 2 entries per month in this table.

+3
source share
4 answers

I think you were discarded by the idea of ​​"averaging." You do not need an average id or an average user_id. You want the average number of entries in the table, so you would use COUNT():

SELECT count(id)/3 AS AverageMonthlyCases
FROM `cases`
WHERE created_dt BETWEEN DATE_SUB(CURDATE(),INTERVAL 90 DAY) AND CURDATE() 
group by doctor_id

90- , 30 /3.

+2
SELECT AVG(cnt), user_id
FROM (
   SELECT COUNT(id) cnt, user_id
   FROM cases
   WHERE created_dt BETWEEN <yourDateInterval>
   GROUP BY user_id, year(created_dt), month(created_dt)
)
+2

, AVG , SUM()/COUNT() , , ( SUM ).

-

SELECT 
    doctor_id, 
    DATE(created_dt,'%m-%Y') AS month, 
    COUNT(id) AS visits
FROM `cases`
GROUP BY 
    `doctor_id`, 
    DATE(created_dt,'%m-%Y')
ORDER BY
    `doctor_id` ASC, 
    DATE(created_dt,'%m-%Y') ASC

. , -

SELECT 
    doctor_id, 
    SUM(visits)/COUNT(month) AS `average` 
FROM (
    SELECT 
        doctor_id, 
        DATE(created_dt,'%m-%Y') AS month, 
        COUNT(id) AS visits
    FROM `cases`
    GROUP BY 
        `doctor_id`, 
        DATE(created_dt,'%m-%Y')
    ORDER BY
        `doctor_id` ASC, 
        DATE(created_dt,'%m-%Y') ASC
    ) t1
GROUP BY
    doctor_id

, WHERE, (.. 2013 2014 ).

, , "" , , (0 ).

+1

, .

 Select monthname(created_dt), doctor_id, count(id) as total from cases group by 1,2 order by 1

You can also use GROUP_CONCAT () as a subquery to expand a table with a rotary element, where each column is each doctor_id.

0
source

More articles:


All Articles