CODEWITHSUNDEEP
mysql
user3411002
user3411002

Reputation: 793

Getting average from a column sql

I have an sql query and wondering how i can implement a sum into this to calculate an average.

I have created this query to output the image below:

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

enter image description here

I want to extend the query to get the average per year for the same patient in a new column or in a new query.

Is this possible? I hope you understand what i want.

Thanks

Upvotes: 0

Views: 58

Answers (2)

Mark Zucchini
Mark Zucchini

Reputation: 925

AVG() SQL function should help you

SELECT d.date, CONCAT_WS(' ', p.PatientFname ,p.PatientLname) AS "Patient",  
       AVG(dc.drugcost_cost) AS "Average", 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 



SELECT d.date, CONCAT_WS(' ', p.PatientFname ,p.PatientLname) AS "Patient",  
       AVG(dc.drugcost_cost) AS "Average", 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 "Patient"
ORDER BY Patient ASC

Upvotes: 0

Matt
Matt

Reputation: 835

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)

To add pc.pharmacy_name to the result:

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

Upvotes: 1

Related Questions