CODEWITHSUNDEEP
mysql
Kurawa
Kurawa

Reputation: 1

MySQL - Group User Base On Total Product

how to group user base on total product

user_table

id    name
--    ----
1     abc
2     def
3     ghi

user_product_table

id   user_id   product
--   -------   --------
1    1         abcd
1    1         efgh
1    1         ijkl
1    2         mnop
1    2         qrst
1    3         uvwx
1    3         yxab

with joining user_table and user_product_table, i will get

user   total_product
----   -------------
abc    3
def    2
ghi    2

simple sql with count(*) and group by user_product_table.user

but, my expected result is

user        total_product
----        -------------
abc         3
def, ghi    2

i maybe have 10000+ record, i'm sure a lot of user have the same total product and i need to grouping user base on their total product

Upvotes: 0

Views: 38

Answers (1)

Seb
Seb

Reputation: 1551

Your query works as expected and gives the correct results. If you want then to group users with the same amount of products together, you can do that in mysql. But if you have so many records, how long will that list be for total_product = 2 or total_product=3. Does that makes sense?

Anyway. To do this just wrap your query (which you do not give unfortunatly) into another query.

I guess this is your existing query:

SELECT u.name, COUNT(p.id) as total_products
FROM user_table u
JOIN user_product_table p ON p.user_id=u.id

And now we group that by name

SELECT GROUP_CONCAT(a.name) as names, a.total_products
FROM (
    SELECT u.name, COUNT(p.id) as total_products
    FROM user_table u
    JOIN user_product_table p ON p.user_id=u.id
) as a
GROUP BY a.total_products

Please be aware that a GROUP_CONCAT column will only display a certain amount of text. If the concated names are longer, some might be missing.

Upvotes: 1

Related Questions