SQL issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL issue

Author

Topic

zoe2003
Starting Member

17 Posts

Posted - 2013-06-10 : 09:12:40

Hi everyone,

I need your help with the below logic:
I have this table:
user milestone
1 0
1 999
2 0
2 2
3 1
3 2
3 999
4 999
5 NULL

It represents users and the milestones they've reached.
I need an sql query that will count distinct :
1. The users who've reached milestones 0 and 999.
2. Reached 999 but not 0.
3. Reached 0 but not 999.
4. Haven't reached none of them.

This is the expected result :

user type count distinct
both 1
no 0 2
no 999 1
missing both 1

Thanks,
Z

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-10 : 10:09:25

[code]
SELECT CASE WHEN MinVal <> MaxVal THEN 'Both'
WHEN MinVal = 0 THEN 'Reached 0 Not 999'
WHEN MinVal = 999 THEN 'Reached 999 Not 0'
WHEN MinVal IS NULL THEN 'Reached None'
END AS Cat,
COUNT(*) AS Cnt
FROM
(
SELECT user,
MIN(CASE WHEN milestone IN (0,999) THEN milestone END) AS MinVal,
MAX(CASE WHEN milestone IN (0,999) THEN milestone END) AS MaxVal
FROM Table
GROUP BY user
)t
GROUP BY CASE WHEN MinVal <> MaxVal THEN 'Both'
WHEN MinVal = 0 THEN 'Reached 0 Not 999'
WHEN MinVal = 999 THEN 'Reached 999 Not 0'
WHEN MinVal IS NULL THEN 'Reached None'
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hommer
Aged Yak Warrior

808 Posts

Posted - 2013-06-10 : 10:41:03

And if you run into "Each GROUP BY expression must contain at least one column that is not an outer reference", just add [] to column name user inside the t.

zoe2003
Starting Member

17 Posts

Posted - 2013-06-10 : 11:08:49

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources