Sum of columns from two GROUP BY Sub-Queries of the same underlying table









up vote
0
down vote

favorite












UPDATE
I determined a significantly more concise and computationally optimal way of expressing this statement. No subqueries no multiple joins.:



SELECT
month(hire_date) as month,
sum(case when gender ="m" then 1 end) as male,
sum(case when gender ="f" then 1 end) as female,
count(*) as total
FROM employees
GROUP BY MONTH(hire_date)



Using the MySQL Employees Sample Database, I am querying the same employees table twice via subquery and cross joining the results to get the pivot table below. I am looking to create a final column that would be the sum of mcount and fcount



Month,Gender,mcount,gender,fcount
'1','M','14310','F','9549'
'2','M','14659','F','9789'
'3','M','16200','F','10717'
'4','M','15363','F','10190'
'5','M','15545','F','10398'
'6','M','15000','F','10003'
'7','M','15260','F','10345'
'8','M','15250','F','10146'
'9','M','14632','F','9684'


I have tried to use subqueries again to create this column, but it's making it evident that my two GROUP BY sub-queries are the wrong way to go about building this pivot table.



SELECT mbm.month, mbm.gender, mbm.count as mcount, fbm.gender, fbm.count as fcount
FROM
(SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "M"
GROUP BY gender,MONTH(hire_date)
) as mbm

CROSS JOIN (SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "F"
GROUP BY gender,MONTH(hire_date)
) AS fbm on mbm.Month = fbm.Month









share|improve this question























  • If male and female are the only values in the gender column, then the sum of the two pivoted column is basically the record count for the given month.
    – Shadow
    Nov 12 at 6:30










  • cross joins don't have an ON clause, this should be written as a full outer join, in case there are months where no males or no females exist. As shadow alludes to, selecting the gender in a query that has only one gender in the where clause, is pointless. select coalesce(mbm.month, fbm.month) as month, mbm.count as malecount, fbm.count as female count from ... full outer join ... on mbm.month = fbm.month
    – Caius Jard
    Nov 12 at 6:38











  • The new column I'm trying to get is indeed the sum for the given month, but what are you suggesting?
    – KLDavenport
    Nov 12 at 6:40










  • Replace the ... with the subqueries. Subqueries simply need to SELECT month(), count() ... where gender = whatever). Remove to group by / select the year if needed
    – Caius Jard
    Nov 12 at 6:44










  • Ps, your query says COUNT but you just said SUM. Decide which it is?
    – Caius Jard
    Nov 12 at 6:45














up vote
0
down vote

favorite












UPDATE
I determined a significantly more concise and computationally optimal way of expressing this statement. No subqueries no multiple joins.:



SELECT
month(hire_date) as month,
sum(case when gender ="m" then 1 end) as male,
sum(case when gender ="f" then 1 end) as female,
count(*) as total
FROM employees
GROUP BY MONTH(hire_date)



Using the MySQL Employees Sample Database, I am querying the same employees table twice via subquery and cross joining the results to get the pivot table below. I am looking to create a final column that would be the sum of mcount and fcount



Month,Gender,mcount,gender,fcount
'1','M','14310','F','9549'
'2','M','14659','F','9789'
'3','M','16200','F','10717'
'4','M','15363','F','10190'
'5','M','15545','F','10398'
'6','M','15000','F','10003'
'7','M','15260','F','10345'
'8','M','15250','F','10146'
'9','M','14632','F','9684'


I have tried to use subqueries again to create this column, but it's making it evident that my two GROUP BY sub-queries are the wrong way to go about building this pivot table.



SELECT mbm.month, mbm.gender, mbm.count as mcount, fbm.gender, fbm.count as fcount
FROM
(SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "M"
GROUP BY gender,MONTH(hire_date)
) as mbm

CROSS JOIN (SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "F"
GROUP BY gender,MONTH(hire_date)
) AS fbm on mbm.Month = fbm.Month









share|improve this question























  • If male and female are the only values in the gender column, then the sum of the two pivoted column is basically the record count for the given month.
    – Shadow
    Nov 12 at 6:30










  • cross joins don't have an ON clause, this should be written as a full outer join, in case there are months where no males or no females exist. As shadow alludes to, selecting the gender in a query that has only one gender in the where clause, is pointless. select coalesce(mbm.month, fbm.month) as month, mbm.count as malecount, fbm.count as female count from ... full outer join ... on mbm.month = fbm.month
    – Caius Jard
    Nov 12 at 6:38











  • The new column I'm trying to get is indeed the sum for the given month, but what are you suggesting?
    – KLDavenport
    Nov 12 at 6:40










  • Replace the ... with the subqueries. Subqueries simply need to SELECT month(), count() ... where gender = whatever). Remove to group by / select the year if needed
    – Caius Jard
    Nov 12 at 6:44










  • Ps, your query says COUNT but you just said SUM. Decide which it is?
    – Caius Jard
    Nov 12 at 6:45












up vote
0
down vote

favorite









up vote
0
down vote

favorite











UPDATE
I determined a significantly more concise and computationally optimal way of expressing this statement. No subqueries no multiple joins.:



SELECT
month(hire_date) as month,
sum(case when gender ="m" then 1 end) as male,
sum(case when gender ="f" then 1 end) as female,
count(*) as total
FROM employees
GROUP BY MONTH(hire_date)



Using the MySQL Employees Sample Database, I am querying the same employees table twice via subquery and cross joining the results to get the pivot table below. I am looking to create a final column that would be the sum of mcount and fcount



Month,Gender,mcount,gender,fcount
'1','M','14310','F','9549'
'2','M','14659','F','9789'
'3','M','16200','F','10717'
'4','M','15363','F','10190'
'5','M','15545','F','10398'
'6','M','15000','F','10003'
'7','M','15260','F','10345'
'8','M','15250','F','10146'
'9','M','14632','F','9684'


I have tried to use subqueries again to create this column, but it's making it evident that my two GROUP BY sub-queries are the wrong way to go about building this pivot table.



SELECT mbm.month, mbm.gender, mbm.count as mcount, fbm.gender, fbm.count as fcount
FROM
(SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "M"
GROUP BY gender,MONTH(hire_date)
) as mbm

CROSS JOIN (SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "F"
GROUP BY gender,MONTH(hire_date)
) AS fbm on mbm.Month = fbm.Month









share|improve this question















UPDATE
I determined a significantly more concise and computationally optimal way of expressing this statement. No subqueries no multiple joins.:



SELECT
month(hire_date) as month,
sum(case when gender ="m" then 1 end) as male,
sum(case when gender ="f" then 1 end) as female,
count(*) as total
FROM employees
GROUP BY MONTH(hire_date)



Using the MySQL Employees Sample Database, I am querying the same employees table twice via subquery and cross joining the results to get the pivot table below. I am looking to create a final column that would be the sum of mcount and fcount



Month,Gender,mcount,gender,fcount
'1','M','14310','F','9549'
'2','M','14659','F','9789'
'3','M','16200','F','10717'
'4','M','15363','F','10190'
'5','M','15545','F','10398'
'6','M','15000','F','10003'
'7','M','15260','F','10345'
'8','M','15250','F','10146'
'9','M','14632','F','9684'


I have tried to use subqueries again to create this column, but it's making it evident that my two GROUP BY sub-queries are the wrong way to go about building this pivot table.



SELECT mbm.month, mbm.gender, mbm.count as mcount, fbm.gender, fbm.count as fcount
FROM
(SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "M"
GROUP BY gender,MONTH(hire_date)
) as mbm

CROSS JOIN (SELECT MONTH(hire_date) as Month, gender, COUNT(*) as count
FROM employees
WHERE gender = "F"
GROUP BY gender,MONTH(hire_date)
) AS fbm on mbm.Month = fbm.Month






mysql sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 16 at 18:39

























asked Nov 12 at 6:21









KLDavenport

547622




547622











  • If male and female are the only values in the gender column, then the sum of the two pivoted column is basically the record count for the given month.
    – Shadow
    Nov 12 at 6:30










  • cross joins don't have an ON clause, this should be written as a full outer join, in case there are months where no males or no females exist. As shadow alludes to, selecting the gender in a query that has only one gender in the where clause, is pointless. select coalesce(mbm.month, fbm.month) as month, mbm.count as malecount, fbm.count as female count from ... full outer join ... on mbm.month = fbm.month
    – Caius Jard
    Nov 12 at 6:38











  • The new column I'm trying to get is indeed the sum for the given month, but what are you suggesting?
    – KLDavenport
    Nov 12 at 6:40










  • Replace the ... with the subqueries. Subqueries simply need to SELECT month(), count() ... where gender = whatever). Remove to group by / select the year if needed
    – Caius Jard
    Nov 12 at 6:44










  • Ps, your query says COUNT but you just said SUM. Decide which it is?
    – Caius Jard
    Nov 12 at 6:45
















  • If male and female are the only values in the gender column, then the sum of the two pivoted column is basically the record count for the given month.
    – Shadow
    Nov 12 at 6:30










  • cross joins don't have an ON clause, this should be written as a full outer join, in case there are months where no males or no females exist. As shadow alludes to, selecting the gender in a query that has only one gender in the where clause, is pointless. select coalesce(mbm.month, fbm.month) as month, mbm.count as malecount, fbm.count as female count from ... full outer join ... on mbm.month = fbm.month
    – Caius Jard
    Nov 12 at 6:38











  • The new column I'm trying to get is indeed the sum for the given month, but what are you suggesting?
    – KLDavenport
    Nov 12 at 6:40










  • Replace the ... with the subqueries. Subqueries simply need to SELECT month(), count() ... where gender = whatever). Remove to group by / select the year if needed
    – Caius Jard
    Nov 12 at 6:44










  • Ps, your query says COUNT but you just said SUM. Decide which it is?
    – Caius Jard
    Nov 12 at 6:45















If male and female are the only values in the gender column, then the sum of the two pivoted column is basically the record count for the given month.
– Shadow
Nov 12 at 6:30




If male and female are the only values in the gender column, then the sum of the two pivoted column is basically the record count for the given month.
– Shadow
Nov 12 at 6:30












cross joins don't have an ON clause, this should be written as a full outer join, in case there are months where no males or no females exist. As shadow alludes to, selecting the gender in a query that has only one gender in the where clause, is pointless. select coalesce(mbm.month, fbm.month) as month, mbm.count as malecount, fbm.count as female count from ... full outer join ... on mbm.month = fbm.month
– Caius Jard
Nov 12 at 6:38





cross joins don't have an ON clause, this should be written as a full outer join, in case there are months where no males or no females exist. As shadow alludes to, selecting the gender in a query that has only one gender in the where clause, is pointless. select coalesce(mbm.month, fbm.month) as month, mbm.count as malecount, fbm.count as female count from ... full outer join ... on mbm.month = fbm.month
– Caius Jard
Nov 12 at 6:38













The new column I'm trying to get is indeed the sum for the given month, but what are you suggesting?
– KLDavenport
Nov 12 at 6:40




The new column I'm trying to get is indeed the sum for the given month, but what are you suggesting?
– KLDavenport
Nov 12 at 6:40












Replace the ... with the subqueries. Subqueries simply need to SELECT month(), count() ... where gender = whatever). Remove to group by / select the year if needed
– Caius Jard
Nov 12 at 6:44




Replace the ... with the subqueries. Subqueries simply need to SELECT month(), count() ... where gender = whatever). Remove to group by / select the year if needed
– Caius Jard
Nov 12 at 6:44












Ps, your query says COUNT but you just said SUM. Decide which it is?
– Caius Jard
Nov 12 at 6:45




Ps, your query says COUNT but you just said SUM. Decide which it is?
– Caius Jard
Nov 12 at 6:45












1 Answer
1






active

oldest

votes

















up vote
0
down vote



accepted










A significantly more concise and computationally optimal way of expressing this statement. No subqueries,no multiple joins, and easy to read:



SELECT
month(hire_date) as month,
sum(case when gender ="m" then 1 end) as male,
sum(case when gender ="f" then 1 end) as female,
count(*) as total
FROM employees
GROUP BY MONTH(hire_date)





share|improve this answer




















    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


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53256832%2fsum-of-columns-from-two-group-by-sub-queries-of-the-same-underlying-table%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








    up vote
    0
    down vote



    accepted










    A significantly more concise and computationally optimal way of expressing this statement. No subqueries,no multiple joins, and easy to read:



    SELECT
    month(hire_date) as month,
    sum(case when gender ="m" then 1 end) as male,
    sum(case when gender ="f" then 1 end) as female,
    count(*) as total
    FROM employees
    GROUP BY MONTH(hire_date)





    share|improve this answer
























      up vote
      0
      down vote



      accepted










      A significantly more concise and computationally optimal way of expressing this statement. No subqueries,no multiple joins, and easy to read:



      SELECT
      month(hire_date) as month,
      sum(case when gender ="m" then 1 end) as male,
      sum(case when gender ="f" then 1 end) as female,
      count(*) as total
      FROM employees
      GROUP BY MONTH(hire_date)





      share|improve this answer






















        up vote
        0
        down vote



        accepted







        up vote
        0
        down vote



        accepted






        A significantly more concise and computationally optimal way of expressing this statement. No subqueries,no multiple joins, and easy to read:



        SELECT
        month(hire_date) as month,
        sum(case when gender ="m" then 1 end) as male,
        sum(case when gender ="f" then 1 end) as female,
        count(*) as total
        FROM employees
        GROUP BY MONTH(hire_date)





        share|improve this answer












        A significantly more concise and computationally optimal way of expressing this statement. No subqueries,no multiple joins, and easy to read:



        SELECT
        month(hire_date) as month,
        sum(case when gender ="m" then 1 end) as male,
        sum(case when gender ="f" then 1 end) as female,
        count(*) as total
        FROM employees
        GROUP BY MONTH(hire_date)






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 13 at 21:57









        KLDavenport

        547622




        547622



























            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.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • 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














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53256832%2fsum-of-columns-from-two-group-by-sub-queries-of-the-same-underlying-table%23new-answer', 'question_page');

            );

            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







            Popular posts from this blog

            Top Tejano songwriter Luis Silva dead of heart attack at 64

            政党

            天津地下鉄3号線