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.
Author |
Topic |
sudheer.v538@gmail.com
Starting Member
10 Posts |
Posted - 2008-05-30 : 08:52:01
|
Hi,I am getting odd result while executing the below query. SELECT COUNT(DISTINCT(D.image_id)), (CASE WHEN D.stage_id IN (SELECT SS_STAGE.stage_id FROM SS_STAGE WHERE SS_STAGE.STAGE_ID = D.STAGE_ID ) THEN (SELECT SS_STAGE.STAGE_ID FROM SS_STAGE WHERE SS_STAGE.STAGE_ID = D.STAGE_ID) ELSE D.stage_id END) stage_idFROM deadline D, OCCURRENCE OWHERE O.image_id = D.image_idAND (D.APPROVED_STAGE IS NULL OR D.CONFLICT = 1)AND D.LOGON = 'pbitest2'AND O.delete_ind = ' 'GROUP BY stage_idMy actual requirement is to group by using the alias name.This query getting the results by grouping the STAGE_ID from DEADLINE table!!!.Please help me on this...Thanks in Advance.Sudheer |
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-30 : 09:12:41
|
you can't group by the alias like this. you need to do it like this:SELECT COUNT(DISTINCT(D.image_id)), stage_id FROM(SELECT COUNT(DISTINCT(D.image_id)), (CASE WHEN D.stage_id IN (SELECT SS_STAGE.stage_id FROM SS_STAGE WHERE SS_STAGE.STAGE_ID = D.STAGE_ID )THEN (SELECT SS_STAGE.STAGE_ID FROM SS_STAGE WHERE SS_STAGE.STAGE_ID = D.STAGE_ID) ELSE D.stage_id END) stage_idFROM deadline D, OCCURRENCE OWHERE O.image_id = D.image_idAND (D.APPROVED_STAGE IS NULL OR D.CONFLICT = 1)AND D.LOGON = 'pbitest2'AND O.delete_ind = ' ') tGROUP BY stage_id_______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-30 : 09:13:17
|
You need to GROUP BY the same extression that defines the value. (rather than the alias)Be One with the OptimizerTG |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-05-30 : 10:43:21
|
Also note that you can directly use the alias name only in ORDER BY clauseOtherwise you need to follow as suggested aboveMadhivananFailing to plan is Planning to fail |
|
|
sudheer.v538@gmail.com
Starting Member
10 Posts |
Posted - 2008-05-30 : 10:50:01
|
Thank you for the valuble suggestion.If i use like this, i am getting the count same for all the records.what can i do to avoid the same count?Sudheer |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-30 : 12:35:46
|
quote: Originally posted by sudheer.v538@gmail.com Thank you for the valuble suggestion.If i use like this, i am getting the count same for all the records.what can i do to avoid the same count?Sudheer
For providing assistance on it we need to know about your table structures with some sample data and sample o/p as per your requirement. |
|
|
|
|
|