Styjun

Sci-fi change: Too much or Not enough

Can a US President, after impeachment and removal, be re-elected or re-appointed?

How to store my pliers and wire cutters on my desk?

Is it error of law to judge on less relevant case law when there is much more relevant one?

Why radial coordinate of a particle must decrease continuously once it is inside the Schwarzschild radius?

How can Paypal know my card is being used in another account?

ECDSA: Why is SigningKey shorter than VerifyingKey

Strange pattern-matching: is it correct?

Exploiting the delay when a festival ticket is scanned

Why would a pilot use ailerons for countering asymmetric thrust in mid-flight?

Why force the nose of 737 Max down in the first place?

Why did I lose on time with 3 pawns vs Knight. Shouldn't it be a draw?

8086 stack segment and avoiding overflow in interrupts

Why does Canada require mandatory bilingualism in a lot of federal government posts?

How likely is fragmentation on a table with 40000 products likely to affect performance

Name These Animals

Increasing labelling size in a ListPointPlot3D

What steps would an amateur scientist have to take in order to get a scientific breakthrough published?

Assuring luggage isn't lost with short layover

Did the Americans trade destroyers in the "destroyer deal" that they would later need themselves?

What's the importance of the plane hijacking to the plot of Suspiria (2018)?

Removing all material slots in one go

Why isn't there any 9.5 digit multimeter or higher?

World of (nearly) identical snowflakes

MySQL view - calculated column from multiple tables returning NULL

Group By Multiple ColumnsUsing group by on multiple columnsError related to only_full_group_by when executing a query in MySqlmysql 5.7 query with group by errorMysql group by doesnt workHow do we select non-aggregate columns in a query with a GROUP BY clause, which is not functionally dependent on columns in GROUP BY clause?After upgrading to mysql 5.7.21: incompatible with sql_mode=only_full_group_bySnorby-Snort-sql_mode=only_full_group_byhow to get single value of group by statement with only_full_group_by enabled in mysqlmysql 5.7 - group by not works as expected

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

Suppose I have a two tables. Table T1 like:

Game Player Points Assists
 1 1 10 5
 1 2 5 10

T2 like:

Game Player Fouls Turnovers
1 1 5 5
1 2 10 10

I'd like to create a view with one row per player and a new field rating, where rating is a equally weighted sum of Points, Assists, Fouls,Turnovers for each player. (i.e. rating = .25 * Points + .25 * Assists + .25 * Fouls + .25 * Turnovers)

I create the view:

CREATE VIEW `player_view` AS (
SELECT Player,
 SUM( 
 Points *0.25 +
 Assists *0.25 +
 Fouls *0.25 +
 Turnovers *0.25) AS Rating)
FROM T1 INNER JOIN T2 ON
 T1.Player = T2.Player
AND T1.Game = T2.Game
GROUP BY Player

But rather than returning a value, I get NULL for all Rating:

Player Rating
1 NULL
2 NULL

Initially, I was faced with

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So I disabled only_full_group_by via SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

So now, although the view returns a results set, the Ratings are all NULL. Please assist.

mysql join group-by calculated-columns sql-view

share|improve this question

edited Mar 26 at 19:51

machump

asked Mar 26 at 19:36

machumpmachump

44333 silver badges1717 bronze badges

add a comment | 

0

Suppose I have a two tables. Table T1 like:

Game Player Points Assists
 1 1 10 5
 1 2 5 10

T2 like:

Game Player Fouls Turnovers
1 1 5 5
1 2 10 10

I'd like to create a view with one row per player and a new field rating, where rating is a equally weighted sum of Points, Assists, Fouls,Turnovers for each player. (i.e. rating = .25 * Points + .25 * Assists + .25 * Fouls + .25 * Turnovers)

I create the view:

CREATE VIEW `player_view` AS (
SELECT Player,
 SUM( 
 Points *0.25 +
 Assists *0.25 +
 Fouls *0.25 +
 Turnovers *0.25) AS Rating)
FROM T1 INNER JOIN T2 ON
 T1.Player = T2.Player
AND T1.Game = T2.Game
GROUP BY Player

But rather than returning a value, I get NULL for all Rating:

Player Rating
1 NULL
2 NULL

Initially, I was faced with

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So I disabled only_full_group_by via SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

So now, although the view returns a results set, the Ratings are all NULL. Please assist.

mysql join group-by calculated-columns sql-view

share|improve this question

edited Mar 26 at 19:51

machump

asked Mar 26 at 19:36

machumpmachump

44333 silver badges1717 bronze badges

add a comment | 

Suppose I have a two tables. Table T1 like:

Game Player Points Assists
 1 1 10 5
 1 2 5 10

T2 like:

Game Player Fouls Turnovers
1 1 5 5
1 2 10 10

I'd like to create a view with one row per player and a new field rating, where rating is a equally weighted sum of Points, Assists, Fouls,Turnovers for each player. (i.e. rating = .25 * Points + .25 * Assists + .25 * Fouls + .25 * Turnovers)

I create the view:

CREATE VIEW `player_view` AS (
SELECT Player,
 SUM( 
 Points *0.25 +
 Assists *0.25 +
 Fouls *0.25 +
 Turnovers *0.25) AS Rating)
FROM T1 INNER JOIN T2 ON
 T1.Player = T2.Player
AND T1.Game = T2.Game
GROUP BY Player

But rather than returning a value, I get NULL for all Rating:

Player Rating
1 NULL
2 NULL

Initially, I was faced with

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

So I disabled only_full_group_by via SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

So now, although the view returns a results set, the Ratings are all NULL. Please assist.

mysql join group-by calculated-columns sql-view

share|improve this question

edited Mar 26 at 19:51

machump

asked Mar 26 at 19:36

machumpmachump

44333 silver badges1717 bronze badges

Game Player Points Assists
 1 1 10 5
 1 2 5 10

Game Player Fouls Turnovers
1 1 5 5
1 2 10 10

CREATE VIEW `player_view` AS (
SELECT Player,
 SUM( 
 Points *0.25 +
 Assists *0.25 +
 Fouls *0.25 +
 Turnovers *0.25) AS Rating)
FROM T1 INNER JOIN T2 ON
 T1.Player = T2.Player
AND T1.Game = T2.Game
GROUP BY Player

Player Rating
1 NULL
2 NULL

share|improve this question

edited Mar 26 at 19:51

machump

asked Mar 26 at 19:36

machumpmachump

44333 silver badges1717 bronze badges

share|improve this question

edited Mar 26 at 19:51

machump

asked Mar 26 at 19:36

machumpmachump

44333 silver badges1717 bronze badges

asked Mar 26 at 19:36

machumpmachump

44333 silver badges1717 bronze badges

asked Mar 26 at 19:36

machumpmachump

44333 silver badges1717 bronze badges

1 Answer
1

active

oldest

votes

0

Seems that you missed an operator between the column in sum eg +
could be you ahve some hidden char in the key fields so.. try trim()

CREATE VIEW `player_view` AS 
SELECT Player,
 SUM( 
 t1.Points*0.25 + 
 t1.Assists*0.25 +
 t2.Fouls*0.25 + 
 t2.Turnovers*0.25) AS Rating
 )
FROM T1 
INNER JOIN T2 ON
 trim(T1.Player) = trim(T2.Player)
 AND trim(T1.Game) = trim(T2.Game);
GROUP BY Player

select * from player_view;

share|improve this answer

edited Mar 26 at 19:51

answered Mar 26 at 19:38

scaisEdgescaisEdge

106k1010 gold badges5555 silver badges7575 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you're right, I wrote the example in haste. My real query w/ real tables has the operators. I will correct it in the first post.
  
  – machump
  Mar 26 at 19:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  answer updated .. with proper group by
  
  – scaisEdge
  Mar 26 at 19:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  answer update using trim .. and you shout restore the full group by clause
  
  – scaisEdge
  Mar 26 at 19:54
  

  
  
  
  

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55365030%2fmysql-view-calculated-column-from-multiple-tables-returning-null%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Seems that you missed an operator between the column in sum eg +
could be you ahve some hidden char in the key fields so.. try trim()

CREATE VIEW `player_view` AS 
SELECT Player,
 SUM( 
 t1.Points*0.25 + 
 t1.Assists*0.25 +
 t2.Fouls*0.25 + 
 t2.Turnovers*0.25) AS Rating
 )
FROM T1 
INNER JOIN T2 ON
 trim(T1.Player) = trim(T2.Player)
 AND trim(T1.Game) = trim(T2.Game);
GROUP BY Player

select * from player_view;

share|improve this answer

edited Mar 26 at 19:51

answered Mar 26 at 19:38

scaisEdgescaisEdge

106k1010 gold badges5555 silver badges7575 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you're right, I wrote the example in haste. My real query w/ real tables has the operators. I will correct it in the first post.
  
  – machump
  Mar 26 at 19:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  answer updated .. with proper group by
  
  – scaisEdge
  Mar 26 at 19:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  answer update using trim .. and you shout restore the full group by clause
  
  – scaisEdge
  Mar 26 at 19:54
  

  
  
  
  

add a comment | 

0

Seems that you missed an operator between the column in sum eg +
could be you ahve some hidden char in the key fields so.. try trim()

CREATE VIEW `player_view` AS 
SELECT Player,
 SUM( 
 t1.Points*0.25 + 
 t1.Assists*0.25 +
 t2.Fouls*0.25 + 
 t2.Turnovers*0.25) AS Rating
 )
FROM T1 
INNER JOIN T2 ON
 trim(T1.Player) = trim(T2.Player)
 AND trim(T1.Game) = trim(T2.Game);
GROUP BY Player

select * from player_view;

share|improve this answer

edited Mar 26 at 19:51

answered Mar 26 at 19:38

scaisEdgescaisEdge

106k1010 gold badges5555 silver badges7575 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  you're right, I wrote the example in haste. My real query w/ real tables has the operators. I will correct it in the first post.
  
  – machump
  Mar 26 at 19:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  answer updated .. with proper group by
  
  – scaisEdge
  Mar 26 at 19:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  answer update using trim .. and you shout restore the full group by clause
  
  – scaisEdge
  Mar 26 at 19:54
  

  
  
  
  

add a comment | 

Seems that you missed an operator between the column in sum eg +
could be you ahve some hidden char in the key fields so.. try trim()

CREATE VIEW `player_view` AS 
SELECT Player,
 SUM( 
 t1.Points*0.25 + 
 t1.Assists*0.25 +
 t2.Fouls*0.25 + 
 t2.Turnovers*0.25) AS Rating
 )
FROM T1 
INNER JOIN T2 ON
 trim(T1.Player) = trim(T2.Player)
 AND trim(T1.Game) = trim(T2.Game);
GROUP BY Player

select * from player_view;

share|improve this answer

edited Mar 26 at 19:51

answered Mar 26 at 19:38

scaisEdgescaisEdge

106k1010 gold badges5555 silver badges7575 bronze badges

CREATE VIEW `player_view` AS 
SELECT Player,
 SUM( 
 t1.Points*0.25 + 
 t1.Assists*0.25 +
 t2.Fouls*0.25 + 
 t2.Turnovers*0.25) AS Rating
 )
FROM T1 
INNER JOIN T2 ON
 trim(T1.Player) = trim(T2.Player)
 AND trim(T1.Game) = trim(T2.Game);
GROUP BY Player

select * from player_view;

share|improve this answer

edited Mar 26 at 19:51

answered Mar 26 at 19:38

scaisEdgescaisEdge

106k1010 gold badges5555 silver badges7575 bronze badges

answered Mar 26 at 19:38

scaisEdgescaisEdge

106k1010 gold badges5555 silver badges7575 bronze badges

answered Mar 26 at 19:38

scaisEdgescaisEdge

106k1010 gold badges5555 silver badges7575 bronze badges