CODEWITHSUNDEEP
mysqlgroup-by
Steve Robbins
Steve Robbins

Reputation: 13812

List ids in group query

I want to return a row for each cluster of data that has a unique amount, operation, months, and fee for a given id.

Table is as follows:

enter image description here

I can almost get what I want with

SELECT amount, operation, months, fee, plan_id
FROM promo_discounts
WHERE promo_id = 1
GROUP BY amount, operation, months, fee

Which will get:

enter image description here

But notice it only returns one plan_id when I want it to return something like 1, 2, 3

Is this possible?

Upvotes: 25

Views: 32117

Answers (4)

Charlestone
Charlestone

Reputation: 1260

What I came up with when dealing with this problem, is to join the group by result with the same table on the fields of the group. Notice that the WHERE statement is the same in both selects

SELECT plan_id
FROM promo_discounts pd 
JOIN (SELECT amount, operation, months, fee
    FROM promo_discounts 
    WHERE promo_id = 1 
    GROUP BY amount, operation, months, fee 
) AS grup ON pd.amount = grup.amount 
             AND pd.operation = grup.operation 
             AND pd.months = grup.months
             AND pd.fee = grup.fee
WHERE promo_id = 1

Now you can play with the result to get exactly what you want

Upvotes: 2

Jin
Jin

Reputation: 566

I wouldn't recommend GROUP_CONCAT, if you need something dynamic. GROUP_CONCAT has a limit. What you could do is use a temp table where you can select all the rows there first, then you can get your grouped results. To get the IDs you can loop the groups and select from the temp table with a WHERE clause that has all the groups as condition. I understand it might be a little bit too complicated for such thing but I sounds to me that it is a good solution if you really need to keep track of the IDs.

Upvotes: 2

ircmaxell
ircmaxell

Reputation: 165191

You could use GROUP_CONCAT

SELECT amount, operation, months, fee, 
    GROUP_CONCAT(DISTINCT plan_id SEPARATOR ',') AS plan_id
FROM promo_discounts
WHERE promo_id = 1
GROUP BY amount, operation, months, fee

Beware though, that the default maximum length is 1024, so if you're doing this with a large table, you could have truncated values...

Upvotes: 56

Thorsten
Thorsten

Reputation: 5644

Have a look at the group_concat() function.

SELECT *, GROUP_CONCAT(id SEPARATOR ",") AS grouped_ids FROM table

Upvotes: 6

Related Questions