SUMming results of a MySQL Query

[adamjnz]

What I am trying to do is get the total of items sold from a table. On the site when the user places an order is stores their username, product, quantity, price etc in a table called order_products.

I have an SQL query that filters the results by the username (as to only get the results for the person in question)

What I need to be able to do is say something like "Hey, since you signed up you have ordered a total of XXXXX products! - Your best selling product is XXXXXX"

I would really appreciate a point in the right direction.

[.josh]

select count(quantity) as totalproducts from tablename where userid = 'blah'

[adamjnz]

This just returns zero (0)

[.josh]

well, did you change that query string to fit your specific table? as in, use the name of your table instead of my generic 'tablename' and use the correct column names?

doing

select count(quantityordered) as totalproducts from tablename where useridcolumn = 'blah'

this will select all rows from useridcolumn and then make a new alias column called totalproducts (which is just an alias of your quantityordered column) and then adds up every entry in that column. so for instance if you have:

table name: products_sold

columns in table:
quantity | user_id
===========
10 | 1
5 | 2
13 | 1

you can see that you have 2 entries in your table associated with user number 1, with a total of 23 items.

so if you do this:

select count(quantity) as total from products_sold where user_id = '1'

then you would get this:

total
===
23

to which you would do something like this in your script:

$sql = "select count(quantity) as total from products_sold where user_id = '1'";
$result = mysql_query($sql) or die(mysql_error());
$total = mysql_fetch_array($result));
echo "you ordered a total of " . $total['total'] . " products.";
//output: you ordered a total of 23 products.


if you have done all this correctly and yet you are still getting a zero value, then are you sure you have entries in your column to even add up?