avgjoe_007 Posted August 9, 2017 Share Posted August 9, 2017 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] Link to comment Share on other sites More sharing options...
Psycho Posted August 9, 2017 Share Posted August 9, 2017 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 Link to comment Share on other sites More sharing options...
Barand Posted August 9, 2017 Share Posted August 9, 2017 I would but an ORDER BY in the group_concat IE GROUP_CONCAT(deviceType ORDER BY deviceType) Link to comment Share on other sites More sharing options...
avgjoe_007 Posted August 10, 2017 Author Share Posted August 10, 2017 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! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.