totals by month even for if there is a missing month

Question

just a quick one, hopefully....

i am after getting some totals (sales value) by month from only a single table.

The problem i have is: If there are no sales for a month, the month is of course not being returned in the results. Is there a way i can do this in a single query so if there were no sales in i.e "January 2015" the result would return "0.00 - January - 2015"

The basic SQL i currently have is:

SELECT SUM(p.PaymentAmount) AS Total, MONTHNAME(p.PaymentDate) AS Month, YEAR(p.PaymentDate) AS Year
FROM tPayment p
WHERE p.PaymentType = 2
GROUP BY YEAR(p.PaymentDate), MONTH(p.PaymentDate)

i cant think of how to do this without selecting the date range in php and then querying each month and year... this just seems messy... so i would like to know if i can do this in a single query.

Any help is much appreciated!

Would it be a problem to fill in any blanks at the PHP side? — Jon, Jan 27 '15 at 23:51
Consider [nested queries](http://www.tutorialspoint.com/sql/sql-sub-queries.htm). — dg99, Jan 27 '15 at 23:53
This looks to be what you're doing, but isn't a simple query http://stackoverflow.com/questions/27600863/mysql-monthly-sale-of-last-12-months-including-months-with-no-sale — Ding, Jan 27 '15 at 23:55
do you need it just for one year? 2014? 2015? or there are many years? — Alex, Jan 28 '15 at 01:20
Thanks all. After looking at all the options, it was after all better to do this in php and not try to make a single query.... thanks for the advice, it is greatly appreciated and useful.!!! — Ford, Jan 28 '15 at 06:26

score 1 · Answer 1 · answered Jan 28 '15 at 00:00

you should create yourself a separate table containing at dates such as

CREATE TABLE `dates` (
    `uid` INT NOT NULL AUTO_INCREMENT,
    `datestamp` DATE NOT NULL,
PRIMARY KEY (`uid`))
ENGINE = InnoDB;

and fill it

INSERT INTO dates (datestamp)
SELECT ADDDATE('2015-01-01', INTERVAL SomeNumber DAY)#set start date
FROM (SELECT a.i+b.i*10+c.i*100+d.i*1000 AS SomeNumber 
    FROM integers a,  integers b, integers c, integers d) Sub1
WHERE SomeNumber BETWEEN 0 AND (365 * 3)#3 years

then you can join against it

SELECT SUM(p.PaymentAmount) AS Total, MONTHNAME(p.PaymentDate) AS Month, YEAR(p.PaymentDate) AS Year
FROM tPayment p
LEFT OUTER JOIN dates d
    ON d.datestamp = CAST(p.PaymentDate AS DATE)
WHERE p.PaymentType = 2
GROUP BY YEAR(p.PaymentDate), MONTH(p.PaymentDate)
ORDER BY d.datestamp DESC;

regardless of if I fatfingered the queries here, the concept should hold up for you

score 0 · Answer 2 · edited May 23 '17 at 12:12

This wouldn't be my first choice method for accomplishing this task, but for the sake of providing multiple alternatives I offer this if you're trying to keep it all in MySQL and avoid creating an additional table.

SELECT 
    SUM(p.PaymentAmount) AS Total, 
    MONTHNAME(p.PaymentDate) AS Month, 
    YEAR(p.PaymentDate) AS Year
FROM ( SELECT 1 AS m UNION ALL 
    SELECT 2 UNION ALL 
    SELECT 3 UNION ALL 
    SELECT 4 UNION ALL 
    SELECT 5 UNION ALL 
    SELECT 6 UNION ALL 
    SELECT 7 UNION ALL 
    SELECT 8 UNION ALL 
    SELECT 9 UNION ALL 
    SELECT 10 UNION ALL 
    SELECT 11 UNION ALL 
    SELECT 12
) AS months
OUTER JOIN tPayment p
    ON MONTH(p.PaymentDate) = months.m
WHERE p.PaymentType = 2
GROUP BY YEAR(p.PaymentDate), MONTH(p.PaymentDate)

I think it'd be easier to check against it in some quick PHP code after you run the query.

As suggested by Jon B Creating a months table and joining against that would shorten and clean the query up quite a bit. If you're trying to keep it all in your MySQL query I personally would choose his method.

score 0 · Answer 3 · answered Jan 28 '15 at 00:17

0

If you have data in your table for all months -- but the where clause is filtering out all the rows from one or more months -- you can try conditional aggregation:

SELECT SUM(CASE WHEN p.PaymentType = 2 THEN p.PaymentAmount ELSE 0 END) AS Total,
       MONTHNAME(p.PaymentDate) AS Month, YEAR(p.PaymentDate) AS Year
FROM tPayment p
GROUP BY YEAR(p.PaymentDate), MONTH(p.PaymentDate)

This isn't guaranteed to work (it depends on the data). But if it does, it is the simplest way to solve this problem.

answered Jan 28 '15 at 00:17

Gordon Linoff

1,242,037
58
646
786

that would work with the current data, but only because there will be at least one form of PaymentType. but it would not work for me if there was not a single payment in any month (although this would never happen), so i would be relying on chance, which may not be good practice for me.... (i dont know why the down vote, seemed a reasonable suggestion/option to me... i have had the same happen to some of my questions in the past.... we all have to learn somewhere.....) – Ford Jan 28 '15 at 06:33
@Ford . . . That is explicitly explained in the answer. This is just a simpler way of getting what you need, which might be possible depending on the data being used. – Gordon Linoff Jan 28 '15 at 12:02

totals by month even for if there is a missing month

3 Answers3