Count multiple columns with unique value

Question

Table A:

id        name        desc
--------------------------------
1        abc        something

2        xyz        something

Table B:

id        nameB        descB        tableA(fk)
-----------------------------------------------
1        ghj        something          1

2        jkl        somethingxyz       1

3        sdf        somesdf            2

4        dfg        somedfg            2

I was able to produce output like

TableA.name TableB.name - Count(TableB.name)

abc              ghj - 1
abc              jkl - 1
xyz              sdf - 1
xyz              dfg - 1

But i want something like this

TableA.name TableB.name - Count(TableB.name)

abc              ghj - 1, jkl - 1
xyz              sdf - 1, dfg - 1

1 is count

Pls help with mysql query.

Seriously consider handling issues of data display in application code — Strawberry
– Strawberry, Commented May 18, 2021 at 11:06

D-Shih · Accepted Answer · 2021-05-18 08:40:26Z

2

You can try to use GROUP_CONCAT function with GROUP BY

Query 1:

SELECT A.name,
       GROUP_CONCAT(CONCAT(b.nameB,'-',cnt) SEPARATOR ',') 
FROM A 
INNER JOIN (
    SELECT  tableA,nameB,COUNT(*) cnt
    FROM B
    GROUP BY tableA,nameB
) b ON A.id = b.tableA
GROUP BY A.name

Results:

| name | GROUP_CONCAT(CONCAT(b.nameB,'-',cnt) SEPARATOR ',') |
|------|-----------------------------------------------------|
|  abc |                                         ghj-1,jkl-1 |
|  xyz |                                         dfg-1,sdf-1 |

answered May 18, 2021 at 8:40

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sharath Nayak Over a year ago

will it be same for sqlite?

D-Shih Over a year ago

Almost the same but it is a little difference on concat string syntax db-fiddle.com/f/cAbFkvTNJJgRYjYYvEbPxe/0

Sharath Nayak Over a year ago

What if i have single table?

Collectives™ on Stack Overflow

Count multiple columns with unique value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related