Mysql select values for all months having no rows for specific months

Hello,
I am having one table which is showing financial data something like this.

[TABLE="width: 500"]
[TR]
[TD]Id
[/TD]
[TD]Date
[/TD]
[TD]Sales
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]26/1/2011
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]15/3/2011
[/TD]
[TD]350
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]28/9/2011
[/TD]
[TD]730
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]20/1/2012
[/TD]
[TD]900
[/TD]
[/TR]
[/TABLE]

I want to retrieve all the months whether sales done or not for every year group by month.

e.g. It should bring every months' records for the year 2011 as well as 2012 with the zero values with the months in which sales is not done.

How can I retrieve such records using mysql query?

Please help me.

Thanks in advance.

 

Let use this statement. It may solve your problem. Tell me if it solve as my PC haven't installed with any SQL software.
"select date, count(Sales) from financial group by date order by date".

 

I think you would need SUM rather than count unless there is a 1 or 0 in the Sales column. Something like this:

SELECT
	Id,
	CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) AS report_month,
	SUM(Sales) AS Sales
FROM
	my_table
GROUP BY
	MONTH (`Date`),
	YEAR (`Date`)
ORDER BY
	YEAR (`Date`) DESC,
	MONTH (`Date`) DESC

Code (markup):

 

I believe what you want belongs to the presentation layer, but if you need to do it in SQL try something like this: create a table that contains a record for every month you would include in the report and LEFT JOIN it with the data from your Sales table summarized by month - this way you would include months without sales. Here is a quick test:

CREATE DATABASE IF NOT EXISTS DATETEST;
USE DATETEST;


CREATE TABLE MONTHS
(
    MNTH INT,
    YR INT,
    PRIMARY KEY (MNTH, YR)
);


INSERT INTO MONTHS VALUES (1, 2011);
INSERT INTO MONTHS VALUES (2, 2011);
INSERT INTO MONTHS VALUES (3, 2011);
INSERT INTO MONTHS VALUES (4, 2011);
INSERT INTO MONTHS VALUES (5, 2011);
INSERT INTO MONTHS VALUES (6, 2011);
INSERT INTO MONTHS VALUES (7, 2011);
INSERT INTO MONTHS VALUES (8, 2011);
INSERT INTO MONTHS VALUES (9, 2011);
INSERT INTO MONTHS VALUES (10, 2011);
INSERT INTO MONTHS VALUES (11, 2011);
INSERT INTO MONTHS VALUES (12, 2011);
INSERT INTO MONTHS VALUES (1, 2012);
INSERT INTO MONTHS VALUES (2, 2012);
INSERT INTO MONTHS VALUES (3, 2012);
INSERT INTO MONTHS VALUES (4, 2012);
INSERT INTO MONTHS VALUES (5, 2012);
INSERT INTO MONTHS VALUES (6, 2012);
INSERT INTO MONTHS VALUES (7, 2012);
INSERT INTO MONTHS VALUES (8, 2012);
INSERT INTO MONTHS VALUES (9, 2012);
INSERT INTO MONTHS VALUES (10, 2012);
INSERT INTO MONTHS VALUES (11, 2012);
INSERT INTO MONTHS VALUES (12, 2012);


CREATE TABLE SALES
(
    ID INT,
    DTE DATETIME,
    QTY INT UNSIGNED,
    PRIMARY KEY (ID)
);


INSERT INTO SALES VALUES (1, '2011-01-26', 500);
INSERT INTO SALES VALUES (2, '2011-03-15', 350);
INSERT INTO SALES VALUES (3, '2011-03-20', 300);
INSERT INTO SALES VALUES (4, '2011-09-28', 730);
INSERT INTO SALES VALUES (5, '2012-01-20', 900);
INSERT INTO SALES VALUES (6, '2012-01-28', 100);


SELECT MONTHS.YR, MONTHS.MNTH, COALESCE(SUM_DATA.QTY, 0) AS MONTHLY_SALES
FROM MONTHS
LEFT JOIN (
    SELECT YEAR(SALES.DTE) AS YR, MONTH(SALES.DTE) AS MNTH, SUM(SALES.QTY) AS QTY
    FROM SALES
    GROUP BY YEAR(SALES.DTE), MONTH(SALES.DTE)) SUM_DATA
ON MONTHS.YR = SUM_DATA.YR
AND MONTHS.MNTH = SUM_DATA.MNTH
ORDER BY MONTHS.YR, MONTHS.MNTH;


DROP TABLE MONTHS;
DROP TABLE SALES;

Code (markup):