CODEWITHSUNDEEP
phpmysql
Ford
Ford

Reputation: 567

totals by month even for if there is a missing month

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!

Upvotes: 0

Views: 193

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 0

Ding
Ding

Reputation: 3085

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.

Upvotes: 0

Jon B
Jon B

Reputation: 507

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

Upvotes: 1

Related Questions