Order by total

[gerkintrigg]

Hi Guys,

 

I'm going to struggle to explain this, but I'll have a go.

 

I want to use a database of book sales to work out which book has made the most money. I want to output all the books based on the sum of the database "price" field.

 

I've tried multiple options, but none of them seem to do exactly what I need.

 

I want the output to look like this:

 

Book 1: ?75,000

Book 2: ?50,000

Book 3: ?25,000

 

I don't know if there's much point showing this code, but here it is anyway:

 

<h2>Top Grossing Books</h2>
<?php 
$q="SELECT `books`.`title`,`book_purchases`.`book_id`, SUM(`book_purchases`.`price`) as `book_sales_total` 
FROM `books`,`book_purchases` 
WHERE `currency`='p' AND `book_id`=`book_id` 
AND `books`.`id`=`book_purchases`.`book_id` 
ORDER BY `book_sales_total` DESC";
$sql=mysql_query($q);
while($r=mysql_fetch_array($sql)){
echo $r['book_id'].': '.$r['book_sales_total'].'<br />';
}
?>

 

I can't really get my head around this one and the Google Searches don't appear to help. Any advice?

[scootstah]

I believe you're looking for GROUP BY.

 

SELECT `books`.`title`,`book_purchases`.`book_id`, SUM(`book_purchases`.`price`) as `book_sales_total` 
FROM `books`,`book_purchases` 
WHERE `currency`='p' AND `book_id`=`book_id` 
AND `books`.`id`=`book_purchases`.`book_id`
GROUP BY `book_purchases`.`book_id`
ORDER BY `book_sales_total` DESC

[Barand]

...AND `book_id`=`book_id` ...

 

 

[scootstah]

...AND `book_id`=`book_id` ...

 

 

 

Gotta make sure 1 == 1.

[Psycho]

@gerkintrigg,

 

In case you didn't catch the meaning from the last few posts, the `book_id`=`book_id` should come out of the query since it is providing no value. I also prefer to use explicit JOINs as it makes reading the query and debugging easier. In the original query I'm pretty sure that it would exclude books that have had no sales. If you want to include those, then you should use a LEFT JOIN. But, if you do want to exclude books with no sales, then use a normal JOIN. Lastly, you prefaced the table name before each field name - except the "currency" field. I'm assuming it is from the "book_purchases". Again, for consistency and readability I would incude the table name there as well. I'd suggest the following:

 

SELECT `books`.`title`, `books`.`id`, SUM(`book_purchases`.`price`) as `book_sales_total` 
FROM `books`
LEFT JOIN `book_purchases`
  ON `books`.`id` = `book_purchases`.`book_id`
WHERE `book_purchases`.`currency`= 'p'
GROUP BY `book_purchases`.`book_id`
ORDER BY `book_sales_total` DESC

[DavidAM]

Along with all the other advice:

 

The back-ticks are completely unnecessary and IMHO make the code harder to read.

 

More importantly --- without seeing your table structure, I have to ask if you are totaling the correct column. If a customer bought 2 books, is the price column the total cost of 2 books, or is it still the unit price (1 book)? If it is the unit price, you need to sum (price * qty) as Total

 

[gerkintrigg]

The Price column accounts for discounts and negotiation so yes it totals correctly.

Thanks for the great advice. I now get it. :-)