A bit difficult query

[mabog]

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.

 

 

[Zane]

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?

[mabog]

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.

 

[Zane]

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.

[mabog]

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?

 

 

[mabog]

Maybe with two queries? One grouped and one without... I have to try that.

[mabog]

I think I got it. The answer is GROUP_CONCAT