CODEWITHSUNDEEP
mysqlsql
Greenback Boogie
Greenback Boogie

Reputation: 93

How can I use CONCAT_WS on grouped queries?

How can I use CONCAT_WS on grouped queries?

Here is my database table:

items

id(PK)  itemcode    req_id(FK)  qualifications  duties

I'm trying to run this query:

SELECT *,CONCAT_WS(' ',itemcode) itemcodes,COUNT(*) counter FROM items
    WHERE req_id=1 GROUP BY qualifications,duties

I'm hoping that the output would be the row plus the itemcode separated by spaces. Say, if there are four rows, I would get: code1 code2 code3 code4 in the itemcodes field. However, it only returns ONE of the item codes.

Sample database entries:

id  code    req_id  qualifications  duties
------------------------------------------------------
1   code12  1       can read        do the laundry
2   code24  1       can read        do the laundry
3   code35  1       can read        do the laundry
4   code47  1       can read        do the laundry
5   code98  1       can write       draw animals
6   code02  1       can write       draw animals
7   code53  2       can sing        sing all the time

Desired output:

for req_id=1

(*) itemcodes                       counter
-------------------------------------------
    code12 code24 code35 code47     4
    code98 code02                   2

Upvotes: 0

Views: 101

Answers (1)

Guillaume Poussel
Guillaume Poussel

Reputation: 9822

Try this:

SELECT qualifications, duties, GROUP_CONCAT(itemcode SEPARATOR ' ') AS itemcodes, COUNT(*) AS counter
FROM items
WHERE req_id = 1
GROUP BY qualifications, duties

GROUP_CONCAT is used to group multiple rows. It is an aggregation function.

CONCAT_WS is used to concatenate multiple strings, using a separator. All strings must be passed as argument, it is not an aggregate function.

Upvotes: 4

Related Questions