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? Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment Share on other sites More sharing options...
rarebit Posted September 16, 2007 Share Posted September 16, 2007 Also try adding 'group by color_no' Quote Link to comment 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... Quote Link to comment 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.