title: How to combine multiple rows with similar ID but varying attributes into a single row using SQL?
body: <p>Context: I have 4 different song charts which show the current trending songs on their respective platforms. I want to aggregate the charts to produce a single chart with a &quot;combined&quot; ranking but I want the weights I give a rank from a particular chart to be configurable. So, I may have a unique song (uniqueness denoted by ISRC) that shows up on all 4 charts. I want to merge all 4 charts first, the aggregate the ranking.</p>
  <p>Question: Currently, I've merged two tables but am unsure how I can merge two rows with the same ISRC into a single row while adding their ranks up? My code so far:</p>
  <pre><code>SELECT * FROM ((SELECT rank,
            isrc,
            song_name,
            dataset_datetime,
            'applemusic' AS SOURCE
     FROM &quot;myTable&quot;
     WHERE chart_country ='US')
  UNION
    (SELECT rank,
            isrc,
            song_name,
            dataset_datetime,
            'spotify' AS SOURCE
     FROM &quot;myTable2&quot;
     WHERE chart_country ='US'))
  ORDER BY cast(rank as int);
  </code></pre>
  <p>Please let me know if any further clarification is required</p>