Getting the wrong AVG () value for a column in sql

Question

I am writing a sql query that will group a column and return its min, max and average. That's what I'm doing:

CREATE TABLE Nums (
patient_id VARCHAR(20),
val DOUBLE
);
INSERT INTO Nums
VALUES ("A", 100), ("A", 175), ("B", 200), ("B", 100), ("B",20), ("B",2000), ("B",4000);

select AVG(event_count), MIN(event_count), MAX(event_count)
from Nums a
join (select patient_id, count(*) as event_count from Nums group by patient_id) b
on a.patient_id = b.patient_id;

I get output for the following query as

AVG(event_count)    MIN(event_count)    MAX(event_count)
4.1429                       2             5

I get the correct values ​​for min and max, however I expected the average to be 3.5. I'm not sure what I'm doing wrong. Any pointers to what I am missing?

Thank.

Answer 1

For ease of understanding, I went further and visually created your tables. Nums a looks like

+ ---------- + ---- +
| patient_id | val  |
+ ---------- + ---- +
| A          | 100  |
| A          | 175  |
| B          | 200  |
| B          | 100  |
| B          | 20   |
| B          | 2000 |
| B          | 4000 |
+ ---------- + ---- +

The table from your subtitle, which you call b , looks like

+ ------------------------- +
| patient_id | event_count  |
+ ---------- + ------------ +
| A          | 2            |
| B          | 5            |
+ ---------- + ------------ +

,

+ ---------- + ---- + ------------ +
| patient_id | val  | event_count  |
+ ---------- + ---- + ------------ +
| A          | 100  | 2            |
| A          | 175  | 2            |
| B          | 200  | 5            |
| B          | 100  | 5            |
| B          | 20   | 5            |
| B          | 2000 | 5            |
| B          | 4000 | 5            |
+ ---------- + ---- + ------------ +

, 29/7 = 4.1429.

, 2 5, , -

select AVG(event_count), MIN(event_count), MAX(event_count)
    from (
        select patient_id, count(*) as event_count
            from Nums
            group by patient_id
        )

Answer 2

SELECT AVG(event_count),MIN(event_count), MAX(event_count)
FROM(
  SELECT patient_id, count(*) as event_count 
  FROM Nums GROUP BY patient_id
)M

Answer 3

select AVG(event_sum), MIN(event_count), MAX(event_count)
from Nums a
join (select patient_id, count(*) as event_count,sum(val) as event_sum from Nums group by patient_id) b
on a.patient_id = b.patient_id;

Answer 4

Yes, please, you can use this query, I think you will get the expected result

select AVG(DISTINCT event_count), MIN(event_count), MAX(event_count)
from Numstest a
join (select patient_id, count(*) as event_count from Numstest group by patient_id) b
on a.patient_id = b.patient_id;