Stumped Missing months in SQL

kiwimediapro · #1 (**permalink**) 06-08-11, 18:10

I have this query:

Code:

SELECT SUM(t1.totalprice) as totalprice,
SUM(t1.sales) as salessubtotal, t1.`year` as `year`,
t1.mnth as `month`
from 
(SELECT
Sum(crm_sales.totalprice) AS totalprice,
MONTHNAME(crm_sales.date) AS mnth,
YEAR(crm_sales.date) AS `year`,
Count(crm_sales.totalprice) AS sales,
MONTH(crm_sales.date) AS `ordering`
FROM `crm_sales`
WHERE `date` BETWEEN '2009-01-01' AND '2010-05-28'
GROUP BY crm_sales.date
) as t1
GROUP BY t1.`year`,t1.mnth
ORDER BY t1.`year`, t1.ordering

TotalPrice Sales Year Month

130567.41 635 2009 May
139533.66 682 2009 June
141245.63 681 2009 July
121226.32 588 2009 August
121582.47 588 2009 September
134636.40 660 2009 October
107815.05 524 2009 November
106716.81 521 2009 December
123851.60 609 2010 January
126752.72 619 2010 February
151362.32 732 2010 March
124616.72 605 2010 April
150702.71 736 2010 May

The query returns the correct data but i need the missing months to be 0 for sales.

ANy ideas

r937 · #2 (**permalink**) 06-08-11, 18:57

create a table with two columns -- year and month -- and populate it with rows for all the years/months that you want to see

then do a LEFT OUTER JOIN to your query as a derived table (subquery in the FROM clause)

vwalah!

kiwimediapro · #3 (**permalink**) 06-09-11, 15:19

Thanks for the reply.

WOuld the table be a temporary table?

If a temp table is possible would I then need to use a stored procedure?

WOuld it work with just the months asthe years are dynamic?

r937 · #4 (**permalink**) 06-09-11, 15:51

Quote:

Originally Posted by kiwimediapro

WOuld the table be a temporary table?

only if you want it to be

Quote:

Originally Posted by kiwimediapro

If a temp table is possible would I then need to use a stored procedure?

no

Quote:

Originally Posted by kiwimediapro

WOuld it work with just the months asthe years are dynamic?

only if you extracted the distinct years dynamically

how would you like to proceed?

kiwimediapro · #5 (**permalink**) 06-09-11, 16:46

The error is:
Table 'crm.crmsales' doesn't exist

Code:

CREATE TEMPORARY TABLE Months ( monthname VARCHAR(50) NOT NULL) ENGINE=MEMORY;

INSERT INTO Months (monthname) VALUES ('January');
INSERT INTO Months (monthname) VALUES ('February');
INSERT INTO Months (monthname) VALUES ('March');
INSERT INTO Months (monthname) VALUES ('April');
INSERT INTO Months (monthname) VALUES ('May');
INSERT INTO Months (monthname) VALUES ('June');
INSERT INTO Months (monthname) VALUES ('July');
INSERT INTO Months (monthname) VALUES ('August');
INSERT INTO Months (monthname) VALUES ('September');
INSERT INTO Months (monthname) VALUES ('October');
INSERT INTO Months (monthname) VALUES ('November');
INSERT INTO Months (monthname) VALUES ('December');



SELECT m.monthname, 
			COALESCE(s.salessubtotal, 0)
FROM months m
LEFT OUTER JOIN
(
SELECT SUM(t1.totalprice) as totalprice,
SUM(t1.sales) as salessubtotal, t1.`year` as `year`,
t1.mnth as `month`
from 
(SELECT
Sum(crmsales.totalprice) AS totalprice,
MONTHNAME(crmsales.date) AS mnth,
YEAR(crmsales.date) AS `year`,
Count(crmsales.totalprice) AS sales,
MONTH(crmsales.date) AS `ordering`
FROM `crmsales`
WHERE `date` BETWEEN '2009-01-01' AND '2010-05-28'
GROUP BY crmsales.date
) as t1
GROUP BY t1.`year`,t1.mnth
ORDER BY t1.`year`, t1.ordering) s 
on m.monthname = s.`month`