Jump to content

join rows


Janthine
Go to solution Solved by fenway,

Recommended Posts

Hello, hope someone can help me out here


 


I have a table,


 


 


Table Scores


 


9687136430_02fc0dcf07_o.jpg


 


First I want to join the rows on ID when the score is the same, so that in the example below ID 2 has one row.


 


9687136884_363fa0cf80_o.jpg


 


But is it also possible to remove the score column and put the score in the different year-columns as below?


 


9683898003_5272668e4a_o.jpg


Thank you


Link to comment
Share on other sites

The year1, year2, year3 columns smell like an error in the design, repeating columns are usually not as flexible as you might want.

 

Your first question could be a simple as running an aggregate:

SELECT id, SUM(year1), SUM(year2), ... FROM table GROUP BY id;

 

The second question requires more rules, you have put A and C in the same row, and all the B's in the second, why can A and C be merged, but the B in year-2 cannnot?

Link to comment
Share on other sites

The first table is an example how I worked it out..so per score there is a row now ...now I want to bring it back per ID and that worked with what you explained

 SELECT id, SUM(year1), SUM(year2), ... FROM table GROUP BY id

and when I write

SUM(year1)>0 year1

it gives the scores per ID in each column.

Now I like to see the different scores in one row but I guess it's won't work out

Link to comment
Share on other sites

  • Solution

This is screaming for normalization, but this will work (link to http://sqlfiddle.com/#!2/30c2b/1/0):

select id
, IFNULL(group_concat(year1),0) as `Year 1`
, IFNULL(group_concat(year2),0) as `Year 2`
, IFNULL(group_concat(year3),0) as `Year 3`
from (
select id
, IF(year1,score,null) as year1
, IF(year2,score,null) as year2
, IF(year3,score,null) as year3
from scores
) t1
group by id;

Obviously, depending on how you want duplicates and sorting, you'll need to tweak the group_concat() call slightly.

Edited by fenway
sqlfiddle
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.