users with greater than 1 device group-CodePudding

I have following table and what I am looking for is users having greater than one device group by device group.

for e.g.

for android user_id 1 and 3 has >1 distinct device group.
for iOS user_id 2, 3 and 4 has >1 distinct device group.
for fireTV user_id 1, 2 has >1 distinct device group.

Table:

*----------------------*
|user_id | device_grp  |
*----------------------*
|    1   |    android  |
|    1   |    fireTV   |  
|    2   |    iOS      |
|    2   |    fireTV   |
|    3   |    android  |
|    3   |    iOS      |
|    4   |    web-play |
|    4   |    iOS      |
|    5   |    android  |
*----------------------*

final result:

*--------------------------------------*
| device_group | users >1 device_grp   |
*--------------------------------------*
|   android    |    2                  |
|   iOS        |    3                  |
|   fireTV     |    2                  |
|   web-play   |    1                  |
*--------------------------------------*

here is the fiddle which is getting result, but is there a better way to achieve above result?

CodePudding user response：

SELECT 
device_grp  as device_group,
COUNT(*) as Total
FROM table
GROUP BY device_grp  
HAVING COUNT(*) > 1

But your example is wrong because web-play total is not greater than 1. If you also need web-play:

SELECT 
device_grp  as device_group,
COUNT(*) as Total
FROM table
GROUP BY device_grp

CodePudding user response：

WITH cnt AS (
  SELECT *, COUNT(*) OVER (PARTITION BY user_id) as user_count
  FROM users
)
SELECT device_grp, COUNT(*) AS ttl
FROM cnt 
WHERE user_count > 1
GROUP BY device_grp

Comparison of query execution plans in the fiddle.