MYSQL query to divide count(*) of both the tables and join by Months over years
0
I want to write an SQL query to divide the count of both the tables and later join them to display months with respect to Months and Year.
I have written a query that divides the count of both the tables, but I am not sure how to combine the date field of both the tables, such that, Months vs the Divided count appears.
select
(a.count_one / b.count_two) as final_count,
a.Months,
b.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY str_to_date(concat(date_format(`first_table `.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY str_to_date(concat(date_format(`second_table`.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
) b
DESIRED OUTPUT:
**Month and Year** **Final_Count**
January 2016 126
February 2016 123
March 2016 45
.... ....
... ....
... ....
mysql
edited Jan 18 at 23:50
GMB
9,2672723
asked Jan 18 at 23:46
Marium MalikMarium Malik
480216
add a comment |
0
I want to write an SQL query to divide the count of both the tables and later join them to display months with respect to Months and Year.
I have written a query that divides the count of both the tables, but I am not sure how to combine the date field of both the tables, such that, Months vs the Divided count appears.
select
(a.count_one / b.count_two) as final_count,
a.Months,
b.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY str_to_date(concat(date_format(`first_table `.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY str_to_date(concat(date_format(`second_table`.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
) b
DESIRED OUTPUT:
**Month and Year** **Final_Count**
January 2016 126
February 2016 123
March 2016 45
.... ....
... ....
... ....
mysql
edited Jan 18 at 23:50
GMB
9,2672723
asked Jan 18 at 23:46
Marium MalikMarium Malik
480216
add a comment |
0
0
0
I want to write an SQL query to divide the count of both the tables and later join them to display months with respect to Months and Year.
I have written a query that divides the count of both the tables, but I am not sure how to combine the date field of both the tables, such that, Months vs the Divided count appears.
select
(a.count_one / b.count_two) as final_count,
a.Months,
b.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY str_to_date(concat(date_format(`first_table `.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY str_to_date(concat(date_format(`second_table`.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
) b
DESIRED OUTPUT:
**Month and Year** **Final_Count**
January 2016 126
February 2016 123
March 2016 45
.... ....
... ....
... ....
mysql
edited Jan 18 at 23:50
GMB
9,2672723
asked Jan 18 at 23:46
Marium MalikMarium Malik
480216
I want to write an SQL query to divide the count of both the tables and later join them to display months with respect to Months and Year.
I have written a query that divides the count of both the tables, but I am not sure how to combine the date field of both the tables, such that, Months vs the Divided count appears.
select
(a.count_one / b.count_two) as final_count,
a.Months,
b.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY str_to_date(concat(date_format(`first_table `.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY str_to_date(concat(date_format(`second_table`.`date`, '%Y-%m'), '-01'), '%Y-%m-%d')
) b
DESIRED OUTPUT:
**Month and Year** **Final_Count**
January 2016 126
February 2016 123
March 2016 45
.... ....
... ....
... ....
mysql
mysql
edited Jan 18 at 23:50
GMB
9,2672723
asked Jan 18 at 23:46
Marium MalikMarium Malik
480216
edited Jan 18 at 23:50
GMB
9,2672723
asked Jan 18 at 23:46
Marium MalikMarium Malik
480216
edited Jan 18 at 23:50
GMB
9,2672723
edited Jan 18 at 23:50
GMB
9,2672723
edited Jan 18 at 23:50
GMB
9,2672723
9,2672723
asked Jan 18 at 23:46
Marium MalikMarium Malik
480216
asked Jan 18 at 23:46
Marium MalikMarium Malik
480216
asked Jan 18 at 23:46
Marium MalikMarium Malik
480216
480216
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
2
You need a joining condition:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Montns)
) b
WHERE a.Months = b.Months
It would be better to learn to write ANSI JOINs:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a
INNER JOIN
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Months)
) b
ON a.Months = b.Months
answered Jan 18 at 23:52
BarmarBarmar
424k35248349
Looks like theGROUP BYclauses are needlessly complicated, and could be simplified asGROUP BY DATE_FORMAT(date, '%M %Y')
– GMB
Jan 19 at 0:03
1
Right, or justGROUP BY Months.
– Barmar
Jan 19 at 0:04
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%2f54262816%2fmysql-query-to-divide-count-of-both-the-tables-and-join-by-months-over-years%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
You need a joining condition:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Montns)
) b
WHERE a.Months = b.Months
It would be better to learn to write ANSI JOINs:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a
INNER JOIN
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Months)
) b
ON a.Months = b.Months
answered Jan 18 at 23:52
BarmarBarmar
424k35248349
Looks like theGROUP BYclauses are needlessly complicated, and could be simplified asGROUP BY DATE_FORMAT(date, '%M %Y')
– GMB
Jan 19 at 0:03
1
Right, or justGROUP BY Months.
– Barmar
Jan 19 at 0:04
add a comment |
2
You need a joining condition:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Montns)
) b
WHERE a.Months = b.Months
It would be better to learn to write ANSI JOINs:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a
INNER JOIN
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Months)
) b
ON a.Months = b.Months
answered Jan 18 at 23:52
BarmarBarmar
424k35248349
Looks like theGROUP BYclauses are needlessly complicated, and could be simplified asGROUP BY DATE_FORMAT(date, '%M %Y')
– GMB
Jan 19 at 0:03
1
Right, or justGROUP BY Months.
– Barmar
Jan 19 at 0:04
add a comment |
2
2
2
You need a joining condition:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Montns)
) b
WHERE a.Months = b.Months
It would be better to learn to write ANSI JOINs:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a
INNER JOIN
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Months)
) b
ON a.Months = b.Months
answered Jan 18 at 23:52
BarmarBarmar
424k35248349
You need a joining condition:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a,
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Montns)
) b
WHERE a.Months = b.Months
It would be better to learn to write ANSI JOINs:
select
(a.count_one / b.count_two) as final_count,
a.Months
from
(
select count(*) as count_one, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from first_table
GROUP BY Months)
) a
INNER JOIN
(
select count(*) as count_two, DATE_FORMAT(`date`, "%M %Y") AS `Months`
from second_table
GROUP BY Months)
) b
ON a.Months = b.Months
answered Jan 18 at 23:52
BarmarBarmar
424k35248349
edited Jan 19 at 0:04
answered Jan 18 at 23:52
BarmarBarmar
424k35248349
answered Jan 18 at 23:52
BarmarBarmar
424k35248349
answered Jan 18 at 23:52
BarmarBarmar
424k35248349
424k35248349
Looks like theGROUP BYclauses are needlessly complicated, and could be simplified asGROUP BY DATE_FORMAT(date, '%M %Y')
– GMB
Jan 19 at 0:03
1
Right, or justGROUP BY Months.
– Barmar
Jan 19 at 0:04
add a comment |
Looks like theGROUP BYclauses are needlessly complicated, and could be simplified asGROUP BY DATE_FORMAT(date, '%M %Y')
– GMB
Jan 19 at 0:03
1
Right, or justGROUP BY Months.
– Barmar
Jan 19 at 0:04
Looks like the
GROUP BY clauses are needlessly complicated, and could be simplified as GROUP BY DATE_FORMAT(date, '%M %Y')– GMB
Jan 19 at 0:03
Looks like the
GROUP BY clauses are needlessly complicated, and could be simplified as GROUP BY DATE_FORMAT(date, '%M %Y')– GMB
Jan 19 at 0:03
1
1
Right, or just
GROUP BY Months.– Barmar
Jan 19 at 0:04
Right, or just
GROUP BY Months.– Barmar
Jan 19 at 0:04
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%2f54262816%2fmysql-query-to-divide-count-of-both-the-tables-and-join-by-months-over-years%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
