Menu
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:
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:
But notice it only returns one plan_id when I want it to return something like 1, 2, 3
Is this possible?
578
Group by is one of the most frequently used SQL clauses. It allows you to collapse a field into its distinct values. This clause is most often used with aggregations to show one value per grouped field or combination of fields. We can use an SQL group by and aggregates to collect multiple types of information.
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
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...
83
Have a look at the group_concat() function.
SELECT *, GROUP_CONCAT(id SEPARATOR ",") AS grouped_ids FROM table
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
