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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

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.