mysql - 从sql列中获取平均值

Question

我有一个 SQL 查询，想知道如何在其中实现求和来计算平均值。

我创建了此查询来输出下面的图像:

SELECT d.date, CONCAT_WS(' ', p.PatientFname ,p.PatientLname) AS "Patient", dc.drugcost_cost, pc.pharmacy_name FROM drugcost dc JOIN patients_copy p ON dc.Patients_copy_idPatients = p.idPatients LEFT JOIN time d ON dc.time_idtime = d.idtime LEFT JOIN pharmacy_location pc ON dc.pharmacy_location_idpharmacy = pc.idpharmacy ORDER BY Patient ASC 

我想扩展查询以获取新列或新查询中同一患者的每年平均值。

这可能吗？我希望你明白我想要什么。

谢谢

Answer 1

SELECT EXTRACT(YEAR FROM date) AS "Year",
       CONCAT_WS(' ', p.PatientFname ,p.PatientLname) AS "Patient", 
       AVG(dc.drugcost_cost) AS "AvgCost"
FROM drugcost dc 
     JOIN patients_copy p ON dc.Patients_copy_idPatients = p.idPatients 
     LEFT JOIN time d ON dc.time_idtime = d.idtime 
     LEFT JOIN pharmacy_location pc ON dc.pharmacy_location_idpharmacy = pc.idpharmacy 
GROUP BY EXTRACT(YEAR FROM date),
         ONCAT_WS(' ', p.PatientFname ,p.PatientLname)

要将 pc.pharmacy_name 添加到结果中:

SELECT EXTRACT(YEAR FROM date) AS "Year",
       CONCAT_WS(' ', p.PatientFname ,p.PatientLname) AS "Patient", 
       AVG(dc.drugcost_cost) AS "AvgCost",
       pc.pharmacy_name
FROM drugcost dc 
     JOIN patients_copy p ON dc.Patients_copy_idPatients = p.idPatients 
     LEFT JOIN time d ON dc.time_idtime = d.idtime 
     LEFT JOIN pharmacy_location pc ON dc.pharmacy_location_idpharmacy = pc.idpharmacy 
GROUP BY EXTRACT(YEAR FROM date),
         ONCAT_WS(' ', p.PatientFname ,p.PatientLname),
         pc.pharmacy_name