How do I create groups and count IDs in SQL?

user_17092418 0
Viewed 26

I need help with writing some SQL code. I have a table organized like the following.

name code
Jill drug
Jill alc
Matt drug
Sally drug
Max alc
Millie other
Rob drug

I need to count how many people (name) are designated to drug only, how many are designated to alc only, and how many are drug and alc. Output should look like:

code Count
drug 3
alc 1
drug & alc 1

So Millie wouldn't be included in these counts bc I just want to look at drug and alc for the code.

Please help I can't figure it out!!

grouping count group-by mysql sql
1 Answers

First look at persons. What are they addicted to? This results in a string of addictions per person, just as shown in your requested result. Then count the persons for all found addiction strings.

select addictions, count(*)
from
(
  select name, group_concat(code order by code separator '&') as addictions
  from mytable
  where code in ('drug', 'alc')
  group by name
) per_name
group by addictions
order by addictions;

I have limited this to 'drug' and 'alc' as requested. You can remove this limit by removing the where clause, if you like.

user_22707620
Related