sql to select one record for every month with a sum of that months records

Question

I have a database that has an "appointments table" and "services table". Each appt has a service and each service has a price. What I would like is one query, that will always return 12 rows (one row for each month) and contain a sum of the months appts (based on it's service id). So far I have:

select sum(service_price) as monthly_total, 
       year(appt_date_time) as year, 
       monthname(appt_date_time) as month 
from appt_tbl 
     join services_tbl on appt_tbl.service_id = services_tbl.service_id 
group by month(appt_date_time), 
         year(appt_date_time) 
order by month(appt_date_time) asc;

Currently, this returns something like:

+---------------+------+-------+
| monthly_total | year | month |
+---------------+------+-------+
|        120.00 | 2012 | July  |
+---------------+------+-------+

The problem is that if a month doesn't have any appts, I do not get that month returned in the query. I would like that month to have a record, just have it's "monthly_total" equal zero.

Below is what I would like the query to return:

+---------------+------+-------+
| monthly_total | year | month |
+---------------+------+-------+
|          0.00 | 2012 | Jan   |
+---------------+------+-------+
|          0.00 | 2012 | Feb   |
+---------------+------+-------+
|          0.00 | 2012 | March |
+---------------+------+-------+
|          0.00 | 2012 | April |
+---------------+------+-------+
|          0.00 | 2012 | May   |
+---------------+------+-------+
|          0.00 | 2012 | June  |
+---------------+------+-------+
|        120.00 | 2012 | July  |
+---------------+------+-------+
|          0.00 | 2012 | August|
+---------------+------+-------+
|          0.00 | 2012 | Sept  |
+---------------+------+-------+
|          0.00 | 2012 | Oct   |
+---------------+------+-------+
|          0.00 | 2012 | Nov   |
+---------------+------+-------+
|          0.00 | 2012 | Dec   |
+---------------+------+-------+

Any ideas?

"what I would like is one query, that will always return 12 rows (one row for each month)" < your original query groups by on year as well as month. Did you mean: 12 rows for each year, or do you really want to bunch up all months across years and literally get 12 rows regardless of any year? — Roland Bouman, Jul 17 '12 at 05:53
If you add table `months` and then join months with service_price, it will do the job for you. I would want to do it without having a month table though. — Ozair Kafray, Jul 17 '12 at 05:54
Also, what bhuvin said. If your appointments table has a row for each date, or at least has each month represented, then a LEFT JOIN should ensure you get all months in the result. — Roland Bouman, Jul 17 '12 at 05:54
Tried left join, no dice. In my app, a new user would not have any appts in the table, so an initial report should be just 0's for each month (report will be run with a year as the context, but that's not represented in my query yet) @roland your correct, I want 12 rows total, one for each month of a given year (lets say year = 2012), thanks for pointing that out! — Greg, Jul 17 '12 at 05:58
left join solve your problem. can you show what you have tried for your left join? — You Qi, Jul 17 '12 at 06:00
@YouQi I just had the same query as above, but with "left" before the join statment, yielded the same result. probably because there was months not represented in the appts table. — Greg, Jul 17 '12 at 06:03

score 3 · Accepted Answer · edited Jul 17 '12 at 07:13

You're on the right track, just LEFT JOIN the result of your query to a table of monthnames. Then the names that exist in your result will return a match, and a NULL will be returned for those months that are not in your result. A COALESCE or NVL or CASE construct, whatever you like, can be used to turn that NULL into a straight 0.

You don't need make a real table of monthts, you can get by using an inline view - simply a query that generates all the month data.

select     months.month, coalesce(appts.monthly_total, 0) monthly_total
from       (
                      select 'January' as month
           union all  select 'February' 
           union all  select 'March'

           ...etc...

           union all  select 'December'
           ) months
left join  (
            select     sum(service_price)        as monthly_total, 
                       monthname(appt_date_time) as month 
            from       appt_tbl 
            inner join services_tbl 
            on         appt_tbl.service_id = services_tbl.service_id
            where      appt_date_time between '2012-01-01 00:00:00'
                                          and '2012-12-31 23:59:59'         
            group by   month(appt_date_time)
           ) appts
on         months.month = appts.month

As for returning everyting for a particular year, look at the WHERE condition. I am asuming appt_date_time is a datetime or timestamp. You don't want to write YEAR(appt_date_time) = 2012 because that will ruin the chances of using an index on that column (I am assuming that column appears as a first column in an index). By using a BETWEEN...AND and comparing against literal datetimes, you can have a pretty efficient query that meets the requirement.

Also, if you GROUP BY on month(appt_date_time) mysql will ensure results will by sorted ascending by month too. So no need for a separate ORDER BY. The order of the 'legs' of the UNION query will also be preserved by mysql.

I think I'm on the same page with you. I've created a table called "months_tbl". The part that is throwing me off is the "coalesce(appts.monthly_total, 0) monthly_total" There is no "monthly_total" field in the appt_tbl. Is this a reference to the left join select select statment? This is really awesome by the way... — Greg, Jul 17 '12 at 06:14
Yes, I wrote: left join (...bla...) appts, so that last appts is an alias for the entire query between parenthesis. So the local name appts refers to that subquery, and in the outer query appts.monthly_total refers to that sum(service_price) expression, because that was given the monthly_total alias in the inner query. — Roland Bouman, Jul 17 '12 at 06:16
Great answer, this works perfectly. Thanks for the quick, and thorough answer! — Greg, Jul 17 '12 at 06:45

score 1 · Answer 2 · answered Jul 17 '12 at 06:25

Try this, it will work for you. All you need to do is to create a lookup table for better approach.

CREATE TABLE MONTHS(MON CHAR(3))
INSERT INTO MONTHS 
SELECT 'Jan' UNION ALL
SELECT 'Feb' UNION ALL
SELECT 'Mar' UNION ALL
SELECT 'Apr' UNION ALL
SELECT 'May' UNION ALL
SELECT 'Jun' UNION ALL
SELECT 'Jul' UNION ALL
SELECT 'Aug' UNION ALL
SELECT 'Sep' UNION ALL
SELECT 'Oct' UNION ALL
SELECT 'Nov' UNION ALL
SELECT 'Dec'

CREATE  TABLE Appointments(App int, Year int, Month CHAR(3))
INSERT INTO Appointments 
SELECT 120,2012,'Jul' UNION ALL 
SELECT 120,2013,'Apr'

SELECT sum(isnull(App,0)) monthly_total, b.Mon , b.Year FROM Appointments a
RIGHT JOIN (SELECT DISTINCT Year,Mon FROM Appointments CROSS JOIN MONTHS) b 
ON a.Month=b.MON AND a.Year=b.Year
GROUP BY b.Mon,b.Year

sql to select one record for every month with a sum of that months records

2 Answers2

Linked