SUGGEST ME A QUERY.... - 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

SUGGEST ME A QUERY....

Author

Topic

anilr499
Starting Member

18 Posts

Posted - 2012-05-07 : 23:19:00

this is my ORIGINAL table name "ANI"...

range score cnt diff

-20 to -16 Low 3 -16
-20 to -16 Moderate 1 -16
-15 to -11 0 0 0
-10 to -6 0 0 0
-5 to -1 0 0 0
0 - 5 Low 1 0
0 - 5 Moderate 6 0
0 - 5 High 1 1
6 - 10 0 0 0
11 - 15 0 0 0
>15 Moderate 2 31
>15 High 1 46
>15 Moderate 1 46
>15 Moderate 1 56
>15 High 2 61
>15 Moderate 1 62

My main intention is to get that data from the table ....

whose score is "low"....and whose score is"moderate".....and whose score is "high".....separately....

and i need to get all the range values which should not be repetitive.....

Some times in mytable ANI ....i may not any of the score parameter.......

for example ...like i may not have "low" for range "-20 to -16"...

still i need to display that "zero" for that range

EG:

select * from ani where SCORE='0' or SCORE='HIGH'

RANGE SCORE CNT

-15 to -11 0 0
-10 to -6 0 0
-5 to -1 0 0
0 - 5 High 1
6 - 10 0 0
11 - 15 0 0
>15 High 1
>15 High 2
>15 High 1

IN THE ABOVE TABLE AN NOT GETTING RANGE "-20 TO -16"....

BUT I REQUIRED THAT RANGE WITH SCORE AND CNT AS "ZERO"

THANK YOU....

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-08 : 07:38:40

If you run this query:

quote:

select * from ani where SCORE='0' or SCORE='HIGH'

You will only get the data where Score = '0' Or Score = 'High'

What do you mean when you say that you want the Data separately for each Score Level??....Do you want a different result set for each Score Level??

Please post the result set which you want from the above Sample Data. That would be easier for us to work with and provide a solution for your requirement.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 11:13:42

sounds like this for me


SELECT t.Range,
t.Score,
COALESCE(t1.cnt,0) AS cnt
FROM
(
SELECT Range,Score
FROM (SELECT DISTINCT Range FROM table)p
CROSS JOIN (SELECT DISTINCT Score FROM table)q
)t
LEFT JOIN table t1
ON t1.Score = t.Score
AND t1.Range = t.Range

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources