MySQL Group and count rows with data

Question

I have a table that holds different kinds of data. what I would like to achieve is that I want to run a query through the database looking for rows that are equal in number and have the same content like say column has id 1 with 3 rows, and column has id 5 with 3 rows and all these values are same. I want to consider that as a group then count how many they are. Rows with different ids but same values. Then have a table that shows count that x matches were found and contains this+this+this values. This is in MySQL

id  |  drug
--------------
1   | LAMIVUDINE
----------------
1   | ZIDOVUDINE
----------------
1   | 3TC
----------------
3   | TTC
-------------
4   | KKY
------------
4   | JJ2
-------------
4   | FFF
---------------
5   | LAMIVUDINE
----------------
5   | ZIDOVUDINE 
-----------------
5   | 3TC
------------------
8   | KKY
----------------
8   | JJ2
--------------
8   | FFF
---------------

count(id) AS number

drug As name

number |  name
-------------------------------------
2      | LAMIVUDINE + ZIDOVUDINE + 3TC
---------------------------------------
2      | KKY + JJ2 + FFF
--------------------------------------`
1      | TTC
---------------------------------------

M Khalid Junaid · Accepted Answer · 2018-06-19 17:50:57Z

1

You can use group_concat with a + separator for concatenate rows with same id and then do another group by on concatenated values to get the desired output

select count(*) number, names
from (
    select count(*) total, group_concat(drug order by drug separator '+') names
    from table1
    group by id
) t 
group by names

demo

Order by clause is important inside group by so that concatenated string values should be in same order

edited Jun 19, 2018 at 17:50

answered Jun 19, 2018 at 17:45

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

Collectives™ on Stack Overflow

MySQL Group and count rows with data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related