Jump to content
avgjoe_007

Best way to do this...

Recommended Posts

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]
 

 

 

 

Share this post


Link to post
Share on other sites

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 by Psycho

Share this post


Link to post
Share on other sites

I would but an ORDER BY in the group_concat

 

IE

GROUP_CONCAT(deviceType ORDER BY deviceType)

Share this post


Link to post
Share on other sites

 

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.