title: Joining Tables and Pivoting Data with MySQL
body: <p>Objective 1:
  Your sales data is stored in the Purchases table.
  Your sales staff wants to see the sales data in a pivoted form, broken down by quarter.</p>
  
  <p>If your Purchases table doesn't have sales data, create some. Be sure the data spans four quarters.
  Next, write a query to pivot the data as follows:</p>
  
  <pre><code>Album              Q1   Q2   Q3   Q4
  
  OK Computer        2    5    3    7
  
   Sea Change        8    6    2    1 
  </code></pre>
  
  <p>Do not create a separate table or a view. Do not alter any tables.</p>
  
  <p>Save your query as dba1lesson10project1.sql and hand in the project.</p>
  
  <p>This is What I need to do. But, the table it wants me to work with looks like this. And it states in the assignment I cannot alter it at all.</p>
  
  <pre><code>CustomerID    DateOfPurchase   SongID
  
  1              2007-03-31        3
  3              2007-06-30        4
  4              2007-09-30        4
  5              2007-12-31        5
  </code></pre>
  
  <p>I know I need to join three tables together so I can group by the title. Which are my Songs, Albums, and Purchases tables.</p>
  
  <pre><code> SELECT Albums.Title FROM Albums
   LEFT JOIN Songs
   INNER JOIN Purchases
   ON Songs.SongID = Purchases.SongID
   ON Albums.Title = Purchases.SongID,
   SELECT Title,
   SUM(CASE WHEN QUARTER(DateOfPurchase) = 1 THEN 1 ELSE 0 END) AS 'Q1',
   SUM(CASE WHEN QUARTER(DateOfPurchase) = 2 THEN 1 ELSE 0 END) AS 'Q2',
   SUM(CASE WHEN QUARTER(DateOfPurchase) = 3 THEN 1 ELSE 0 END) AS 'Q3',
   SUM(CASE WHEN QUARTER(DateOfPurchase) = 4 THEN 1 ELSE 0 END) AS 'Q4'
   From Purchases
   GROUP BY Title;
  </code></pre>
  
  <p>I'm kind of at a loss here when it comes to Joining three separate tables then pivoting the data</p>
  
  <p>I've tried the code above in multiple other variants which has failed me past the table joining portion.</p>
  
  <p>Any help would be much appreciated.</p>