Best way to do this...

[avgjoe_007]

Hi all

 

Been a long time since I've done any SQL, having moved into project management. I've got a data set that I want to slice and I'm a bit stumped on how to get this report out. Any idea how I can do this?

 

This is the data I have:

 

UserID

DeviceType

1

a

1

b

1

c

2

a

2

b

2

c

3

a

3

b

4

d

 

The is the output I'm looking for 

 

Count

ConfigType

2

a,b,c

1

a,b

1

d

[/td]

[td]

 

 

 

 

[Psycho]

Here's one solution:

 

First create a query using GROUP_CONCAT() to derive the device combinations for each user ID. Then use that query as a sub-query source to determine the count of users that have each unique device configuration.

 

 

SELECT COUNT(UserID) as UserCount, ConfigType
 
FROM (
    SELECT UserID, GROUP_CONCAT(`DeviceType`) as ConfigType
    FROM votes
    GROUP BY UserID
) as DerivedTable
 
GROUP BY ConfigType

Edited August 9, 2017 by Psycho

[Barand]

I would but an ORDER BY in the group_concat

 

IE

GROUP_CONCAT(deviceType ORDER BY deviceType)

[avgjoe_007]

 

Here's one solution:

 

First create a query using GROUP_CONCAT() to derive the device combinations for each user ID. Then use that query as a sub-query source to determine the count of users that have each unique device configuration.

SELECT COUNT(UserID) as UserCount, ConfigType
 
FROM (
    SELECT UserID, GROUP_CONCAT(`DeviceType`) as ConfigType
    FROM votes
    GROUP BY UserID
) as DerivedTable
 
GROUP BY ConfigType

 

Hey, just wanted to say thanks! You're a lifesaver!