Trouble making chart that shows Sales by Year

I’m attempting to make a chart which has the following headers.

.

Now, I know how to make one chart which limits the activity to one particular year, but I can’t seem to figure out how to piece it all together in one place.

I have imagined that I need to select all at the beginning, but then somehow limit the reporting for each yearly column. Here’s what I have so far:

echo "<table class=\\"sortable\\" style='width:100%' 
border=\\"1px\\" id=\\"dataTable\\" >";

echo "<tr>
<th>Company</th>
<th>Total Amount</th>
<th>2010</th>
<th>2009</th>
<th>2008</th>
<th>2007</th>
<th>2006</th>
<th>2005</th>
</tr>";

$query = "SELECT DISTINCT company, SUM(amount) 
FROM sales 
GROUP BY company 
ORDER by SUM(amount) DESC";


$result = mysql_query($query);


if (!$result) {

    $message  = 'Invalid query: ' . mysql_error() . "\
";

    $message .= 'Whole query: ' . $query;

    die($message);

}


while ($row = mysql_fetch_assoc($result))
 {


echo "<tr><td><b>".$row['company']."</b></td>
<td>$".number_format($row['SUM(amount)'],0)."</td>
<td>$".number_format($row['SUM(amount)'],0)."</td>
<td>$".number_format($row['SUM(amount)'],0)."</td>
<td>$".number_format($row['SUM(amount)'],0)."</td>
<td>$".number_format($row['SUM(amount)'],0)."</td>
<td>$".number_format($row['SUM(amount)'],0)."</td>
<td>$".number_format($row['SUM(amount)'],0)."</td>
</tr>";

}


?>
</table>

i know that what I have is going to give me the exact same total amount in each column, but I just have no idea where to begin to be able to limit the results in each column to the total for each corresponding year.

Anyone able to lead me in some sort of direction?

Mucho!

where’s the sales date column? how can you do sales by year if you don’t have a sales date column?

um, ya, sorry, i dropped it on the floor, here ya go…

SELECT company
     , [COLOR="Blue"]SUM(amount) AS TotalAmount[/COLOR]
     , SUM(CASE WHEN purchasedate LIKE '2010%' 
                THEN amount 
                ELSE NULL END) AS "2010" 
     , ...

omg it worked!!! TY TY TY! I’m going to have to do some reading that explains it a little more so I can use it more down the road, but this is perfect.

I lost the total column, but I’m sure I can figure out how to get that back. I will try again after getting home from work.

Mucho X1000

<td>$“.number_format($row[‘2010’],0).”</td>
<td>$“.number_format($row[‘2009’],0).”</td>
<td>$“.number_format($row[‘2008’],0).”</td>
<td>$“.number_format($row[‘2007’],0).”</td>
<td>$“.number_format($row[‘2006’],0).”</td>
<td>$“.number_format($row[‘2005’],0).”</td>

Oh… very nice! Thank you! I don’t think I’ve read about that code as of yet, but I will have to give it a try but before I do… How would I need to change

<td>$“.number_format($row[‘SUM(amount)’],0).”</td>

to get it show the 2010 sum?

SELECT company
     , SUM(CASE WHEN purchasedate LIKE '2010&#37;' 
                THEN amount 
                ELSE NULL END) AS "2010" 
     , SUM(CASE WHEN purchasedate LIKE '2009%' 
                THEN amount 
                ELSE NULL END) AS "2009" 
     , SUM(CASE WHEN purchasedate LIKE '2008%' 
                THEN amount 
                ELSE NULL END) AS "2008" 
     , SUM(CASE WHEN purchasedate LIKE '2007%' 
                THEN amount 
                ELSE NULL END) AS "2007" 
     , SUM(CASE WHEN purchasedate LIKE '2006%' 
                THEN amount 
                ELSE NULL END) AS "2006" 
     , SUM(CASE WHEN purchasedate LIKE '2005%' 
                THEN amount 
                ELSE NULL END) AS "2005" 
  FROM sales 
GROUP 
    BY company 

what does your sql table look like?

purchase date.

I only store the year and month in a variable as I’m not storing the exact date. I’ve limited other reports with this:

$query = “SELECT * FROM sales WHERE purchasedate LIKE ‘2010%’ ORDER BY purchasedate DESC”;

Structure:

Example:

perfecto!

Next up, to get the total of each column at top. Now don’t tell me! I’m going to figure it out damn it!