How to get the correct sql select function for the following task

Question

I need to display the total sales of 2014 for each car brand on the following webpage:

http://test.com/example/2014/8.

htaccess file:

RewriteRule ^example/([^/]+)/([^/]+)$ test?year=$1&monthnumber=$2 [L,QSA]

Example data (carbrand, year, monthnumber, sales):

carbrand  year  month  sales
----------------------------
Mercedes  2014     8    2800
BMW       2014     8    3000
Audi      2014     8    2700
Mercedes  2014     7    2600
BMW       2014     7    2900
Audi      2014     7    2400

Html/php:

<?php
    $year= $_GET['year'];
    $monthnumber= $_GET['monthnumber'];

(Now the tricky part)

    $sql = "SELECT ........

I need the sql select function to do 3 things:

step 1: find all different car brands that sold vehicles in 2014 => SELECT distinct carbrand FROM example-table WHERE year = :year
step 2: calculate the total sales for each of those distinct car brands in that year => select "SUM(sales)) for each car brand" WHERE year = :year

step 3: order the results by those total sales, desc => ORDER BY "SUM(sales) for each car brand" DESC

$stmt = $pdo->prepare($sql);
$stmt->bindParam(":year", $year);
$stmt->execute();

if($result = $stmt->fetch(PDO::FETCH_ASSOC))
{
?>

<?php echo $result['carbrand'];?> <?php echo $result['(sum sales for that brand in a given year'];?>

<?php
}// end if
else {
echo '0 results';
}// end else
?>

The result of my example should be:

BMW, 5900
Mercedes, 5400
Audi, 5100

I can't figure out how to write the correct sql function that gives me these results. More specifically I don't get how I can include the distinct car brands in step 2 and step 3.

Can anyone help me out with this sql function?

Without table layout/data, there's no way to give an accurate answer. — James

VMai VMai · Accepted Answer · 2014-09-22T17:16:02

Your considerations were nearly right. Only the first one "Distinct" was wrong. As pointed out in the comments, you should use GROUP BY:

SELECT
    carbrand,
    SUM(sales) totals
FROM
    your_table
WHERE
    year = 2014
GROUP BY
    carbrand
ORDER BY
    totals DESC

Demo

If you're using aggregate functions like i.e. SUM() you nearly ever want to GROUP your data. Because of that you find these functions in the chapter Functions and Modifiers for Use with GROUP BY Clauses.

How to get the correct sql select function for the following task

1 Answers