JJohnsenDK Posted September 16, 2007 Share Posted September 16, 2007 Hey Im trying to find the highest amount for products in my stock and i want to find it by products_id and color_no. By that i mean that one product can have many different colors so i want to sort the amount of products after which color they have. Here is my database: stock_id - 1 products_id - 3 color_no - 55555 units_in - 20 units_out - 0 date - 15/10-07 stock_id - 2 products_id - 3 color_no - 66666 units_in - 10 units_out - 0 date - 15/10-07 stock_id - 3 products_id - 3 color_no - 55555 units_in - 0 units_out - 5 date - 15/10-07 stock_id - 4 products_id - 3 color_no - 66666 units_in - 0 units_out - 5 date - 15/10-07 So here i got 4 actions in my database. I have recived 10 items of products 3 with the color no 66666 and 20 items of product 3 with the color_no 55555 and then later sold 5 items of the product with color no 66666. Later i sell 5 items of the same product but this product have the color no 55555. This gives me a total of 5 items with the color no 66666 and 15 items with the color no 55555 at my stock. How do i add this up and only echo out the item with the highest amout of items? Link to comment https://forums.phpfreaks.com/topic/69559-some-tricky-database-calculating-i-think-p/ Share on other sites More sharing options...
rarebit Posted September 16, 2007 Share Posted September 16, 2007 Take a look at these functions: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html Link to comment https://forums.phpfreaks.com/topic/69559-some-tricky-database-calculating-i-think-p/#findComment-349531 Share on other sites More sharing options...
JJohnsenDK Posted September 16, 2007 Author Share Posted September 16, 2007 yeah... i know of these functions but i dont think i can use them? because i have the units_in and units_out colums. And i cant multiply this in the mysql query like this: mysql_query("SELECT SUM(units_in) AS units_in, SUM(units_out) AS units_out FROM stock WHERE products_id = '$product' and color_no = '$color'"); this would only give me the sum of units_in and units_out for one of the products. Link to comment https://forums.phpfreaks.com/topic/69559-some-tricky-database-calculating-i-think-p/#findComment-349539 Share on other sites More sharing options...
rarebit Posted September 16, 2007 Share Posted September 16, 2007 Also try adding 'group by color_no' Link to comment https://forums.phpfreaks.com/topic/69559-some-tricky-database-calculating-i-think-p/#findComment-349543 Share on other sites More sharing options...
JJohnsenDK Posted September 16, 2007 Author Share Posted September 16, 2007 that still would not help because i somehow need to loop through that query to see which product has the most amount of items... Link to comment https://forums.phpfreaks.com/topic/69559-some-tricky-database-calculating-i-think-p/#findComment-349551 Share on other sites More sharing options...
rarebit Posted September 16, 2007 Share Posted September 16, 2007 I might be wrong but it might be two queries then, one for item totals and another for item totals based on colour? Link to comment https://forums.phpfreaks.com/topic/69559-some-tricky-database-calculating-i-think-p/#findComment-349565 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.