CODEWITHSUNDEEP
mysqlsqldatabase
Effektz
Effektz

Reputation: 115

GROUP_CONCAT a select statement

So here's the SQL I have

SELECT c.name AS 'name',
  COUNT(sc.condition_id) AS 'conditions_count'
FROM reviews r, strain_conditions sc
LEFT JOIN conditions c ON sc.condition_id = c.id
WHERE sc.strain_id = 1 && sc.review_id = r.id && r.moderated = 1
GROUP BY sc.condition_id
ORDER BY conditions_count DESC
LIMIT 3;

Which returns

+---------------+------------------+
| name          | conditions_count |
+---------------+------------------+
| Fiber Myalgia |                2 |
| Anxiety       |                2 |
| ADHD          |                1 |
+---------------+------------------+

What I need to do is GROUP_CONCAT the name results so my end result is simply

"Fiber Myalgia, Anxiety, ADHD"

Upvotes: 3

Views: 324

Answers (3)

Mark Byers
Mark Byers

Reputation: 838056

You need nested selects because the LIMIT should only apply to the subquery.

SELECT GROUP_CONCAT(name) AS name
FROM
(
    SELECT c.name AS name
    FROM reviews r, strain_conditions sc
    LEFT JOIN conditions c ON sc.condition_id = c.id
    WHERE sc.strain_id = 1 && sc.review_id = r.id && r.moderated = 1
    GROUP BY sc.condition_id
    ORDER BY conditions_count DESC
    LIMIT 3  
) AS T1

If you want them ordered by descending count you should add an ORDER BY to the GROUP_CONCAT.

SELECT GROUP_CONCAT(name ORDER BY conditions_count DESC) AS name
FROM
(
     -- your query here
) AS T1

If you also want the space after the comma, use SEPARATOR:

SELECT GROUP_CONCAT(name ORDER BY conditions_count DESC SEPARATOR ', ') AS name
FROM
(
     -- your query here
) AS T1

For more details, see the manual page for GROUP_CONCAT.

Upvotes: 3

Taryn
Taryn

Reputation: 247650

You need to place that in a sub-select:

SELECT GROUP_CONCAT(name) AS name
FROM
(
  SELECT c.name AS 'name',
    COUNT(sc.condition_id) AS 'conditions_count'
  FROM reviews r, strain_conditions sc
  LEFT JOIN conditions c ON sc.condition_id = c.id
  WHERE sc.strain_id = 1 && sc.review_id = r.id && r.moderated = 1
  GROUP BY sc.condition_id
  ORDER BY conditions_count DESC
  LIMIT 3
) x

Upvotes: 1

John Woo
John Woo

Reputation: 263693

Wrap it in a subquery. try this,

SELECT GROUP_CONCAT(f.name) as `name`
FROM
(
    SELECT c.name AS 'name',
           COUNT(sc.condition_id) AS 'conditions_count'
    FROM reviews r, strain_conditions sc
    LEFT JOIN conditions c ON sc.condition_id = c.id
    WHERE sc.strain_id = 1 && sc.review_id = r.id && r.moderated = 1
    GROUP BY sc.condition_id
    ORDER BY conditions_count DESC
    LIMIT 3
) f

Upvotes: 3

Related Questions