MySQL query to get last 12 months data grouped by month including zero counts
0
I have table orders (order_id, order_processed_date). I want the count of orders per month for last 12 months. If any month has 0 orders, then it should print 0.
Something like this:
Total Orders | Month
-----------------------
2 | OCT 2018
3 | SEP 2018
0 | AUG 2018
0 | JUL 2018
1 | JUN 2018
0 | MAY 2018
0 | APR 2018
0 | MAR 2018
5 | FEB 2018
5 | JAN 2018
0 | DEC 2017
0 | NOV 2017
I already searched in this website and get some queries but did not find exact solution for this. I get result using below query but it does not have year:
SELECT
SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
SUM(IF(month = 'May', total, 0)) AS 'May',
SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM (SELECT DATE_FORMAT(order_processed_date, '%b') AS month,
COUNT(order_id) as total
FROM orders
WHERE order_processed_date <= NOW() and order_processed_date >=
Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(order_processed_date, '%m-%Y')) as sub
Can please someone help me to give me result as I required.
mysql sql datetime group-by
edited Jan 1 at 8:37
Salman A
181k66339432
asked Oct 19 '18 at 11:51
Ketan LathiyaKetan Lathiya
4151621
add a comment |
0
I have table orders (order_id, order_processed_date). I want the count of orders per month for last 12 months. If any month has 0 orders, then it should print 0.
Something like this:
Total Orders | Month
-----------------------
2 | OCT 2018
3 | SEP 2018
0 | AUG 2018
0 | JUL 2018
1 | JUN 2018
0 | MAY 2018
0 | APR 2018
0 | MAR 2018
5 | FEB 2018
5 | JAN 2018
0 | DEC 2017
0 | NOV 2017
I already searched in this website and get some queries but did not find exact solution for this. I get result using below query but it does not have year:
SELECT
SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
SUM(IF(month = 'May', total, 0)) AS 'May',
SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM (SELECT DATE_FORMAT(order_processed_date, '%b') AS month,
COUNT(order_id) as total
FROM orders
WHERE order_processed_date <= NOW() and order_processed_date >=
Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(order_processed_date, '%m-%Y')) as sub
Can please someone help me to give me result as I required.
mysql sql datetime group-by
edited Jan 1 at 8:37
Salman A
181k66339432
asked Oct 19 '18 at 11:51
Ketan LathiyaKetan Lathiya
4151621
1
You need another master table to store all the months, and take that as your first table in the left join.
– Madhur Bhaiya
Oct 19 '18 at 11:53
@MadhurBhaiya What about years ? only just need to store months only ?
– Ketan Lathiya
Oct 19 '18 at 11:54
Don't you need data only for last 12 months ? You can useYEAR(CURRENT_DATE())to get current year and subtract it by 1 to get previous year. This will be enough to cover 12 months
– Madhur Bhaiya
Oct 19 '18 at 11:58
You can also emulate Month table usingselect 'jan' union select 'feb' union 'mar' ...
– Madhur Bhaiya
Oct 19 '18 at 11:59
Can you give me example query or update in above query if possible.
– Ketan Lathiya
Oct 19 '18 at 12:01
add a comment |
0
0
0
I have table orders (order_id, order_processed_date). I want the count of orders per month for last 12 months. If any month has 0 orders, then it should print 0.
Something like this:
Total Orders | Month
-----------------------
2 | OCT 2018
3 | SEP 2018
0 | AUG 2018
0 | JUL 2018
1 | JUN 2018
0 | MAY 2018
0 | APR 2018
0 | MAR 2018
5 | FEB 2018
5 | JAN 2018
0 | DEC 2017
0 | NOV 2017
I already searched in this website and get some queries but did not find exact solution for this. I get result using below query but it does not have year:
SELECT
SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
SUM(IF(month = 'May', total, 0)) AS 'May',
SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM (SELECT DATE_FORMAT(order_processed_date, '%b') AS month,
COUNT(order_id) as total
FROM orders
WHERE order_processed_date <= NOW() and order_processed_date >=
Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(order_processed_date, '%m-%Y')) as sub
Can please someone help me to give me result as I required.
mysql sql datetime group-by
edited Jan 1 at 8:37
Salman A
181k66339432
asked Oct 19 '18 at 11:51
Ketan LathiyaKetan Lathiya
4151621
I have table orders (order_id, order_processed_date). I want the count of orders per month for last 12 months. If any month has 0 orders, then it should print 0.
Something like this:
Total Orders | Month
-----------------------
2 | OCT 2018
3 | SEP 2018
0 | AUG 2018
0 | JUL 2018
1 | JUN 2018
0 | MAY 2018
0 | APR 2018
0 | MAR 2018
5 | FEB 2018
5 | JAN 2018
0 | DEC 2017
0 | NOV 2017
I already searched in this website and get some queries but did not find exact solution for this. I get result using below query but it does not have year:
SELECT
SUM(IF(month = 'Jan', total, 0)) AS 'Jan',
SUM(IF(month = 'Feb', total, 0)) AS 'Feb',
SUM(IF(month = 'Mar', total, 0)) AS 'Mar',
SUM(IF(month = 'Apr', total, 0)) AS 'Apr',
SUM(IF(month = 'May', total, 0)) AS 'May',
SUM(IF(month = 'Jun', total, 0)) AS 'Jun',
SUM(IF(month = 'Jul', total, 0)) AS 'Jul',
SUM(IF(month = 'Aug', total, 0)) AS 'Aug',
SUM(IF(month = 'Sep', total, 0)) AS 'Sep',
SUM(IF(month = 'Oct', total, 0)) AS 'Oct',
SUM(IF(month = 'Nov', total, 0)) AS 'Nov',
SUM(IF(month = 'Dec', total, 0)) AS 'Dec'
FROM (SELECT DATE_FORMAT(order_processed_date, '%b') AS month,
COUNT(order_id) as total
FROM orders
WHERE order_processed_date <= NOW() and order_processed_date >=
Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(order_processed_date, '%m-%Y')) as sub
Can please someone help me to give me result as I required.
mysql sql datetime group-by
mysql sql datetime group-by
edited Jan 1 at 8:37
Salman A
181k66339432
asked Oct 19 '18 at 11:51
Ketan LathiyaKetan Lathiya
4151621
edited Jan 1 at 8:37
Salman A
181k66339432
asked Oct 19 '18 at 11:51
Ketan LathiyaKetan Lathiya
4151621
edited Jan 1 at 8:37
Salman A
181k66339432
edited Jan 1 at 8:37
Salman A
181k66339432
edited Jan 1 at 8:37
Salman A
181k66339432
181k66339432
asked Oct 19 '18 at 11:51
Ketan LathiyaKetan Lathiya
4151621
asked Oct 19 '18 at 11:51
Ketan LathiyaKetan Lathiya
4151621
asked Oct 19 '18 at 11:51
Ketan LathiyaKetan Lathiya
4151621
4151621
1
You need another master table to store all the months, and take that as your first table in the left join.
– Madhur Bhaiya
Oct 19 '18 at 11:53
@MadhurBhaiya What about years ? only just need to store months only ?
– Ketan Lathiya
Oct 19 '18 at 11:54
Don't you need data only for last 12 months ? You can useYEAR(CURRENT_DATE())to get current year and subtract it by 1 to get previous year. This will be enough to cover 12 months
– Madhur Bhaiya
Oct 19 '18 at 11:58
You can also emulate Month table usingselect 'jan' union select 'feb' union 'mar' ...
– Madhur Bhaiya
Oct 19 '18 at 11:59
Can you give me example query or update in above query if possible.
– Ketan Lathiya
Oct 19 '18 at 12:01
add a comment |
1
You need another master table to store all the months, and take that as your first table in the left join.
– Madhur Bhaiya
Oct 19 '18 at 11:53
@MadhurBhaiya What about years ? only just need to store months only ?
– Ketan Lathiya
Oct 19 '18 at 11:54
Don't you need data only for last 12 months ? You can useYEAR(CURRENT_DATE())to get current year and subtract it by 1 to get previous year. This will be enough to cover 12 months
– Madhur Bhaiya
Oct 19 '18 at 11:58
You can also emulate Month table usingselect 'jan' union select 'feb' union 'mar' ...
– Madhur Bhaiya
Oct 19 '18 at 11:59
Can you give me example query or update in above query if possible.
– Ketan Lathiya
Oct 19 '18 at 12:01
1
1
You need another master table to store all the months, and take that as your first table in the left join.
– Madhur Bhaiya
Oct 19 '18 at 11:53
You need another master table to store all the months, and take that as your first table in the left join.
– Madhur Bhaiya
Oct 19 '18 at 11:53
@MadhurBhaiya What about years ? only just need to store months only ?
– Ketan Lathiya
Oct 19 '18 at 11:54
@MadhurBhaiya What about years ? only just need to store months only ?
– Ketan Lathiya
Oct 19 '18 at 11:54
Don't you need data only for last 12 months ? You can use
YEAR(CURRENT_DATE()) to get current year and subtract it by 1 to get previous year. This will be enough to cover 12 months– Madhur Bhaiya
Oct 19 '18 at 11:58
Don't you need data only for last 12 months ? You can use
YEAR(CURRENT_DATE()) to get current year and subtract it by 1 to get previous year. This will be enough to cover 12 months– Madhur Bhaiya
Oct 19 '18 at 11:58
You can also emulate Month table using
select 'jan' union select 'feb' union 'mar' ...– Madhur Bhaiya
Oct 19 '18 at 11:59
You can also emulate Month table using
select 'jan' union select 'feb' union 'mar' ...– Madhur Bhaiya
Oct 19 '18 at 11:59
Can you give me example query or update in above query if possible.
– Ketan Lathiya
Oct 19 '18 at 12:01
Can you give me example query or update in above query if possible.
– Ketan Lathiya
Oct 19 '18 at 12:01
add a comment |
1 Answer
1
active
oldest
votes
2
The following query builds a list of 1st day of month for previous 12 months. For example on Oct 19 2018 the list will contain dates from Oct 01 2018 to Nov 01 2017. Left join does the rest:
SELECT date, COUNT(orders.primary_key)
FROM (
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN orders ON order_processed_date >= date AND order_processed_date < date + INTERVAL 1 MONTH
GROUP BY date
answered Oct 19 '18 at 12:04
Salman ASalman A
181k66339432
Perfect! got required data! Thanks.
– Ketan Lathiya
Oct 20 '18 at 6:59
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52891799%2fmysql-query-to-get-last-12-months-data-grouped-by-month-including-zero-counts%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
The following query builds a list of 1st day of month for previous 12 months. For example on Oct 19 2018 the list will contain dates from Oct 01 2018 to Nov 01 2017. Left join does the rest:
SELECT date, COUNT(orders.primary_key)
FROM (
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN orders ON order_processed_date >= date AND order_processed_date < date + INTERVAL 1 MONTH
GROUP BY date
answered Oct 19 '18 at 12:04
Salman ASalman A
181k66339432
Perfect! got required data! Thanks.
– Ketan Lathiya
Oct 20 '18 at 6:59
add a comment |
2
The following query builds a list of 1st day of month for previous 12 months. For example on Oct 19 2018 the list will contain dates from Oct 01 2018 to Nov 01 2017. Left join does the rest:
SELECT date, COUNT(orders.primary_key)
FROM (
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN orders ON order_processed_date >= date AND order_processed_date < date + INTERVAL 1 MONTH
GROUP BY date
answered Oct 19 '18 at 12:04
Salman ASalman A
181k66339432
Perfect! got required data! Thanks.
– Ketan Lathiya
Oct 20 '18 at 6:59
add a comment |
2
2
2
The following query builds a list of 1st day of month for previous 12 months. For example on Oct 19 2018 the list will contain dates from Oct 01 2018 to Nov 01 2017. Left join does the rest:
SELECT date, COUNT(orders.primary_key)
FROM (
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN orders ON order_processed_date >= date AND order_processed_date < date + INTERVAL 1 MONTH
GROUP BY date
answered Oct 19 '18 at 12:04
Salman ASalman A
181k66339432
The following query builds a list of 1st day of month for previous 12 months. For example on Oct 19 2018 the list will contain dates from Oct 01 2018 to Nov 01 2017. Left join does the rest:
SELECT date, COUNT(orders.primary_key)
FROM (
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN orders ON order_processed_date >= date AND order_processed_date < date + INTERVAL 1 MONTH
GROUP BY date
answered Oct 19 '18 at 12:04
Salman ASalman A
181k66339432
edited Oct 19 '18 at 13:23
answered Oct 19 '18 at 12:04
Salman ASalman A
181k66339432
answered Oct 19 '18 at 12:04
Salman ASalman A
181k66339432
answered Oct 19 '18 at 12:04
Salman ASalman A
181k66339432
181k66339432
Perfect! got required data! Thanks.
– Ketan Lathiya
Oct 20 '18 at 6:59
add a comment |
Perfect! got required data! Thanks.
– Ketan Lathiya
Oct 20 '18 at 6:59
Perfect! got required data! Thanks.
– Ketan Lathiya
Oct 20 '18 at 6:59
Perfect! got required data! Thanks.
– Ketan Lathiya
Oct 20 '18 at 6:59
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f52891799%2fmysql-query-to-get-last-12-months-data-grouped-by-month-including-zero-counts%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You need another master table to store all the months, and take that as your first table in the left join.
– Madhur Bhaiya
Oct 19 '18 at 11:53
@MadhurBhaiya What about years ? only just need to store months only ?
– Ketan Lathiya
Oct 19 '18 at 11:54
Don't you need data only for last 12 months ? You can use
YEAR(CURRENT_DATE())to get current year and subtract it by 1 to get previous year. This will be enough to cover 12 months– Madhur Bhaiya
Oct 19 '18 at 11:58
You can also emulate Month table using
select 'jan' union select 'feb' union 'mar' ...– Madhur Bhaiya
Oct 19 '18 at 11:59
Can you give me example query or update in above query if possible.
– Ketan Lathiya
Oct 19 '18 at 12:01