Jump to content


Photo

SUMming results of a MySQL Query


  • Please log in to reply
3 replies to this topic

#1 adamjnz

adamjnz
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 10 May 2006 - 04:31 AM

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.

#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 10 May 2006 - 04:49 AM

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

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 adamjnz

adamjnz
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts

Posted 11 May 2006 - 02:51 AM

This just returns zero (0)

#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 11 May 2006 - 06:06 AM

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?
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users