MySQL: Joining to Subquery on YEAR(date)












0















Trying to create a table that summarizes data from another one. The summary is to be by year that is extracted from a date field. When I try to include a new field to the table with a subquery, I get a 1054 (unknown column) error in the ON clause. I have tried joining on date and get the same error.



CREATE TABLE IF NOT EXISTS park_factor (
SELECT YEAR(games.date) AS year
, games.home_team
, sum(games.runs0) AS ht_runs
, sum(games.runs1) as away_runs
, round(leagues.rules_schedule_games_per_team/2,0) AS home_games
, x.home_losses
FROM games
INNER JOIN leagues ON games.league_id=leagues.league_id
INNER JOIN (
SELECT YEAR(games.date) as year
, games.home_team
, COUNT(*) as home_losses
FROM games
WHERE games.runs1>runs0
GROUP BY year, games.home_team
) AS x ON x.year=games.year AND x.home_team=games.home_team
GROUP BY year, games.home_team, home_games, home_losses );









share|improve this question



























    0















    Trying to create a table that summarizes data from another one. The summary is to be by year that is extracted from a date field. When I try to include a new field to the table with a subquery, I get a 1054 (unknown column) error in the ON clause. I have tried joining on date and get the same error.



    CREATE TABLE IF NOT EXISTS park_factor (
    SELECT YEAR(games.date) AS year
    , games.home_team
    , sum(games.runs0) AS ht_runs
    , sum(games.runs1) as away_runs
    , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
    , x.home_losses
    FROM games
    INNER JOIN leagues ON games.league_id=leagues.league_id
    INNER JOIN (
    SELECT YEAR(games.date) as year
    , games.home_team
    , COUNT(*) as home_losses
    FROM games
    WHERE games.runs1>runs0
    GROUP BY year, games.home_team
    ) AS x ON x.year=games.year AND x.home_team=games.home_team
    GROUP BY year, games.home_team, home_games, home_losses );









    share|improve this question

























      0












      0








      0








      Trying to create a table that summarizes data from another one. The summary is to be by year that is extracted from a date field. When I try to include a new field to the table with a subquery, I get a 1054 (unknown column) error in the ON clause. I have tried joining on date and get the same error.



      CREATE TABLE IF NOT EXISTS park_factor (
      SELECT YEAR(games.date) AS year
      , games.home_team
      , sum(games.runs0) AS ht_runs
      , sum(games.runs1) as away_runs
      , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
      , x.home_losses
      FROM games
      INNER JOIN leagues ON games.league_id=leagues.league_id
      INNER JOIN (
      SELECT YEAR(games.date) as year
      , games.home_team
      , COUNT(*) as home_losses
      FROM games
      WHERE games.runs1>runs0
      GROUP BY year, games.home_team
      ) AS x ON x.year=games.year AND x.home_team=games.home_team
      GROUP BY year, games.home_team, home_games, home_losses );









      share|improve this question














      Trying to create a table that summarizes data from another one. The summary is to be by year that is extracted from a date field. When I try to include a new field to the table with a subquery, I get a 1054 (unknown column) error in the ON clause. I have tried joining on date and get the same error.



      CREATE TABLE IF NOT EXISTS park_factor (
      SELECT YEAR(games.date) AS year
      , games.home_team
      , sum(games.runs0) AS ht_runs
      , sum(games.runs1) as away_runs
      , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
      , x.home_losses
      FROM games
      INNER JOIN leagues ON games.league_id=leagues.league_id
      INNER JOIN (
      SELECT YEAR(games.date) as year
      , games.home_team
      , COUNT(*) as home_losses
      FROM games
      WHERE games.runs1>runs0
      GROUP BY year, games.home_team
      ) AS x ON x.year=games.year AND x.home_team=games.home_team
      GROUP BY year, games.home_team, home_games, home_losses );






      mysql






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Nov 20 '18 at 23:44









      JayCo741JayCo741

      226




      226
























          2 Answers
          2






          active

          oldest

          votes


















          0














          There is no games.year column, which is what is causing your error. You need to JOIN using



           x.year = YEAR(games.date) AND x.home_team=games.home_team





          share|improve this answer































            0














            I figured it out while @Nick was answering. The code that works is:



            CREATE TABLE IF NOT EXISTS park_factor (
            SELECT YEAR(games.date) AS `year`
            , games.home_team
            , sum(games.runs0) AS ht_runs
            , sum(games.runs1) as away_runs
            , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
            , x.home_losses
            FROM games
            INNER JOIN leagues ON games.league_id=leagues.league_id
            INNER JOIN (
            SELECT YEAR(games.date) as `year`
            , games.home_team
            , COUNT(*) as home_losses
            FROM games
            WHERE games.runs1>runs0
            GROUP BY year, games.home_team
            ) AS x ON x.`year`=YEAR(games.date) AND x.home_team=games.home_team
            GROUP BY YEAR(games.date), games.home_team, home_games, home_losses );





            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%2f53403285%2fmysql-joining-to-subquery-on-yeardate%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              0














              There is no games.year column, which is what is causing your error. You need to JOIN using



               x.year = YEAR(games.date) AND x.home_team=games.home_team





              share|improve this answer




























                0














                There is no games.year column, which is what is causing your error. You need to JOIN using



                 x.year = YEAR(games.date) AND x.home_team=games.home_team





                share|improve this answer


























                  0












                  0








                  0







                  There is no games.year column, which is what is causing your error. You need to JOIN using



                   x.year = YEAR(games.date) AND x.home_team=games.home_team





                  share|improve this answer













                  There is no games.year column, which is what is causing your error. You need to JOIN using



                   x.year = YEAR(games.date) AND x.home_team=games.home_team






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 20 '18 at 23:53









                  NickNick

                  35k132143




                  35k132143

























                      0














                      I figured it out while @Nick was answering. The code that works is:



                      CREATE TABLE IF NOT EXISTS park_factor (
                      SELECT YEAR(games.date) AS `year`
                      , games.home_team
                      , sum(games.runs0) AS ht_runs
                      , sum(games.runs1) as away_runs
                      , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
                      , x.home_losses
                      FROM games
                      INNER JOIN leagues ON games.league_id=leagues.league_id
                      INNER JOIN (
                      SELECT YEAR(games.date) as `year`
                      , games.home_team
                      , COUNT(*) as home_losses
                      FROM games
                      WHERE games.runs1>runs0
                      GROUP BY year, games.home_team
                      ) AS x ON x.`year`=YEAR(games.date) AND x.home_team=games.home_team
                      GROUP BY YEAR(games.date), games.home_team, home_games, home_losses );





                      share|improve this answer




























                        0














                        I figured it out while @Nick was answering. The code that works is:



                        CREATE TABLE IF NOT EXISTS park_factor (
                        SELECT YEAR(games.date) AS `year`
                        , games.home_team
                        , sum(games.runs0) AS ht_runs
                        , sum(games.runs1) as away_runs
                        , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
                        , x.home_losses
                        FROM games
                        INNER JOIN leagues ON games.league_id=leagues.league_id
                        INNER JOIN (
                        SELECT YEAR(games.date) as `year`
                        , games.home_team
                        , COUNT(*) as home_losses
                        FROM games
                        WHERE games.runs1>runs0
                        GROUP BY year, games.home_team
                        ) AS x ON x.`year`=YEAR(games.date) AND x.home_team=games.home_team
                        GROUP BY YEAR(games.date), games.home_team, home_games, home_losses );





                        share|improve this answer


























                          0












                          0








                          0







                          I figured it out while @Nick was answering. The code that works is:



                          CREATE TABLE IF NOT EXISTS park_factor (
                          SELECT YEAR(games.date) AS `year`
                          , games.home_team
                          , sum(games.runs0) AS ht_runs
                          , sum(games.runs1) as away_runs
                          , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
                          , x.home_losses
                          FROM games
                          INNER JOIN leagues ON games.league_id=leagues.league_id
                          INNER JOIN (
                          SELECT YEAR(games.date) as `year`
                          , games.home_team
                          , COUNT(*) as home_losses
                          FROM games
                          WHERE games.runs1>runs0
                          GROUP BY year, games.home_team
                          ) AS x ON x.`year`=YEAR(games.date) AND x.home_team=games.home_team
                          GROUP BY YEAR(games.date), games.home_team, home_games, home_losses );





                          share|improve this answer













                          I figured it out while @Nick was answering. The code that works is:



                          CREATE TABLE IF NOT EXISTS park_factor (
                          SELECT YEAR(games.date) AS `year`
                          , games.home_team
                          , sum(games.runs0) AS ht_runs
                          , sum(games.runs1) as away_runs
                          , round(leagues.rules_schedule_games_per_team/2,0) AS home_games
                          , x.home_losses
                          FROM games
                          INNER JOIN leagues ON games.league_id=leagues.league_id
                          INNER JOIN (
                          SELECT YEAR(games.date) as `year`
                          , games.home_team
                          , COUNT(*) as home_losses
                          FROM games
                          WHERE games.runs1>runs0
                          GROUP BY year, games.home_team
                          ) AS x ON x.`year`=YEAR(games.date) AND x.home_team=games.home_team
                          GROUP BY YEAR(games.date), games.home_team, home_games, home_losses );






                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Nov 20 '18 at 23:58









                          JayCo741JayCo741

                          226




                          226






























                              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














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403285%2fmysql-joining-to-subquery-on-yeardate%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

                              Guess what letter conforming each word

                              Port of Spain

                              Run scheduled task as local user group (not BUILTIN)