CODEWITHSUNDEEP
mysqlsql
Dudo1985
Dudo1985

Reputation: 289

Mysql Select two columns in a virtual one

I've this query

SELECT post_id, set_type, field_id, votes
FROM birr_multi_values
WHERE post_id IN (
    SELECT post_id 
    FROM birr_multi_values
    GROUP BY post_id 
    HAVING 0<>SUM(votes)
)
ORDER BY post_id, set_type, field_id DESC

that it's showing this result:

query result

What I want to obtain is something like this

enter image description here

I would like to create a virtual column with the values from the "field_id" and "votes".

How can I do this?

Upvotes: 0

Views: 52

Answers (1)

forpas
forpas

Reputation: 164139

You must GROUP BY post_id, set_type and use GROUP_CONCAT():

SELECT post_id, set_type, 
  GROUP_CONCAT(
    CONCAT('field_id:',field_id, ', votes:', votes)
    ORDER BY field_id DESC
    SEPARATOR '; '
  ) new_virtual_column 
FROM birr_multi_values
WHERE post_id IN (
    SELECT post_id 
    FROM birr_multi_values
    GROUP BY post_id 
    HAVING 0<>SUM(votes)
)
GROUP BY post_id, set_type
ORDER BY post_id, set_type

See the demo.

Upvotes: 1

Related Questions