mabog Posted March 9, 2010 Share Posted March 9, 2010 A bit difficult (?) query. Is this even possible? Quantity values and importance values is needed to add together if next user(s) code & color are the same. But also I need to show user's comments for every individual data. And sorted by importance. Data: IDcodedescriptionquantity colorimportanceusercomment 1123item122black99user1aaaaaa 2898item3500red30user1bbbbbb 3898item399red50user4cccccc 4898item3400red8user8dddddd 566item5555black4user1eeeeee 6777itemx1red26user9ffffff 7777itemx2red30user4gggggg Output: [/td]IDcodedescriptionquantitycolorimportanceuseruser's comment row1:1123item122black99user1aaaaaa row2:2898item3999red88user1bbbbbb row3:user4cccccc row4:user8dddddd row5:7777itemx3red56user4gggggg row6:user9ffffff row5:566item5555black4user1eeeeee I have been able to create that output with php (with mysql_data_seek and with a reqular query) but without "importance" sorting. Link to comment https://forums.phpfreaks.com/topic/194605-a-bit-difficult-query/ Share on other sites More sharing options...
Zane Posted March 9, 2010 Share Posted March 9, 2010 A bit difficult (?) query. Is this even possible? Quantity values and importance values is needed to add together if next user(s) code & color are the same. But also I need to show user's comments for every individual data. And sorted by importance. It must be a bit difficult, because am lost on your explanation. You want importance and quantity to be added together ONLY if the next user's code and color values are equal? But also I need to show user's comments for every individual data. Now why would you have a problem with this? Link to comment https://forums.phpfreaks.com/topic/194605-a-bit-difficult-query/#findComment-1023533 Share on other sites More sharing options...
mabog Posted March 9, 2010 Author Share Posted March 9, 2010 It must be a bit difficult, because am lost on your explanation. Sorry my bad explanation / english. You want importance and quantity to be added together ONLY if the next user's code and color values are equal? You can see in the data table that when code & color are the same, those items quantities are summed. Same thing with importance. Not quantity + importance. Like this: user1's quantity + user4's quantity + user8's quantity user1's importance + user4's importance + user8's importance Output explained: Row1 is the first because it has the highest importance value. And there's no other case where code is 123 AND color is black. Rows2-4: Three users has code 898:s with red color. Total quantity (500+99+400) is 999 and total importance (30+50+ is 88. Rows5-6: Two users has code 777:s with red color. Total quantity is (1 + 2) 3 and importance (26+30) value 56. Row 7 is last because lowest importance value. And there's no other code 66 WITH black color. Link to comment https://forums.phpfreaks.com/topic/194605-a-bit-difficult-query/#findComment-1023553 Share on other sites More sharing options...
Zane Posted March 9, 2010 Share Posted March 9, 2010 Sorry, I'm still not quite understanding you. But I think your looking for the GROUP BY function and the ORDER BY function keep in mind you can GROUP and ORDER by multiple columns. Link to comment https://forums.phpfreaks.com/topic/194605-a-bit-difficult-query/#findComment-1023559 Share on other sites More sharing options...
mabog Posted March 9, 2010 Author Share Posted March 9, 2010 Sorry, I'm still not quite understanding you. But I think your looking for the GROUP BY function and the ORDER BY function OK, I almost got it. With: ... SUM(quantity), SUM(importance) ... GROUP BY code,color ORDER BY importance desc Output is now this: [/td]IDcodedescriptionquantitycolorimportanceuseruser's comment row1:1123item122black99 row2:2898item3999red88user1bbbbbb row3:7777itemx3red56 row4:566item5555black4user1eeeeee But as can you see, I'm missing users and their comments where data is grouped. How can I extract those too? Link to comment https://forums.phpfreaks.com/topic/194605-a-bit-difficult-query/#findComment-1023604 Share on other sites More sharing options...
mabog Posted March 9, 2010 Author Share Posted March 9, 2010 Maybe with two queries? One grouped and one without... I have to try that. Link to comment https://forums.phpfreaks.com/topic/194605-a-bit-difficult-query/#findComment-1023609 Share on other sites More sharing options...
mabog Posted March 9, 2010 Author Share Posted March 9, 2010 I think I got it. The answer is GROUP_CONCAT Link to comment https://forums.phpfreaks.com/topic/194605-a-bit-difficult-query/#findComment-1023748 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.