title: Need to fetch data from a really messed up mysql table
body: <p>Table 1:</p>
  
  <pre><code>id  |  video_id  | group_id  |  img
  =======================================
  1   |  1         | 1         |  
  2   |  2         | 1         | abc.png 
  3   |  3         | 2         | bef.png 
  4   |  4         | 3         | dca.png
  5   |  5         | 1         |  
  6   |  6         | 2         |  
  7   |  8         | 1         |  
  </code></pre>
  
  <p>Table 2:-</p>
  
  <pre><code>video_id  | week  |  count  |  type
  ===================================
  1         | 2     |  2      |  0
  2         | 2     |  1      |  1
  1         | 3     |  5      |  0
  1         | 3     |  3      |  1
  3         | 3     |  4      |  0
  1         | 3     |  1      |  0
  </code></pre>
  
  <p>Now I know this structure is messed up and needs to be normalized, but as I don't have modification rights to it, I am stuck with this. So please bear with me.</p>
  
  <p>As you can see, the first table stores multiple videos within a group, and only one video in each group can have an associated image.
  The second table stores the type and count of video views on a per week basis.</p>
  
  <p>Now I need a mysql query to fetch SUM of counts, the video id, and the image present against the group id of this video for a particular week. The problem is, the image is stored against ONE video of a group, which may or may not have the highest sum views, but still the query will need to fetch the image against whichever video has the image for that group along with the video id and other info of the video with the max view count.</p>
  
  <p>Any ideas?</p>