SQL problem with 12 subquery

Question

Exists such db schema:

I need to write query.

For every doctor i need average cost of visit by month for 2009 year. The result is (name_of_doctor, january, febriary, ..., december)

I know how to do this with 12 subquery. Exists another more convinient way?

Answer 1

Try the following. You may need to modify the date functions depending on your RDBMS. I have assumed MySQL, but the rest should be universal SQL.

SELECT doctors.name, monthly.average, monthly.month
FROM doctors JOIN (
  SELECT AVG(cost), MONTH(visit_date) AS month FROM visits
  WHERE YEAR(visit_date) GROUP BY MONTH(visit_date)
) AS monthly ON doctors.id = visits.id_doc

Note, this may only include months for doctors that have visits. So you may need to use IFNULL or COALESCE to clean up your output.

Answer 2

You can do it easily as below - but it will list each months cost in a separate line .If you want the costs on the same line you can use a pivot statement .This is for SQL 2008 if you neede pivoting we can do it.If there is a performance problem use a range scan on date rather than use datepart

select d.name,datepart(month,v.visit_date) as month,
avg(v.cost) as avgcost
from visits as v inner join 
doctors as d on v.id_doc=d.id
and datepart(year,v.visit_date)=2010
group by d.name,datepart(month,v.visit_date)

Answer 3

I would just do a normal select with a GROUP BY over the month and have your UI handle displaying it as 12 columns across. If you really need to do it though, then this should work:

SELECT
    D.name,
    AVG(CASE WHEN MONTH(V.visit_date) = 1 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 2 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 3 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 4 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 5 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 6 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 7 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 8 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 9 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 10 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 11 THEN V.cost ELSE NULL END),
    AVG(CASE WHEN MONTH(V.visit_date) = 12 THEN V.cost ELSE NULL END)
FROM
    Doctors D
INNER JOIN Visits V ON
    V.id_doc = D.id AND
    V.visit_date BETWEEN '2009-01-01' AND '2009-12-31'
GROUP BY
    D.name
ORDER BY
    D.name

You might need to change the date functions based on your RDBMS. Also, you may need to fiddle with the edge cases - if your dates have a time component it won't catch rows on 12/31.

Finally, I don't know if this changes between RDBMSs and I can't test right now, but if AVG counts a NULL as 0 cost instead of discounting them then you may need to do your own averag - SUM(CASE ... cost ... 0)/SUM(CASE ... 1 ... 0). I hope that makes sense.