Ranking a Case Statement - 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

Ranking a Case Statement

Author

Topic

dversloot
Starting Member

3 Posts

Posted - 2012-08-01 : 15:30:20

I've managed to write a query to sum the units sold in a season, but I am now having difficulty ranking the seasons as they are in different columns.
Any suggestions?

Select
Units.Item,
Sum(case sea.season when 1 then Units.UnitsSold end) as s1,
Sum(case sea.season when 2 then Units.UnitsSold end) as s2,
Sum(case sea.season when 3 then Units.UnitsSold end) as s3,
Sum(case sea.season when 4 then Units.UnitsSold end) as s4

from
UnitsSoldbyWeek Units,
Season sea

where
sea.Period = Units.period and
sea.Week = Units.week and
Units.Year = 11 and
Units.Item = abc a

Units.Item

Output right now resemble
Item S1 S2 S3 S4
ABC 25 12 56 06

I'd like it to output
Item S1 S2 S3 S4
ABC 2 3 1 4

Dan Versloot
Business Analyst

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-01 : 15:42:39

Are you trying to rank the COLUMNS or the ROWS? I assume rows, but your sample only has one row, so maybe that assumption is incorrect. Can you show more sample data or explaine in words how you expect to rank, whatever you are trying rank?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-01 : 16:31:39

[code]
Select
Item,
Min(case season when 1 then Rnk end) as s1,
Min(case season when 2 then Rnk end) as s2,
Min(case season when 3 then Rnk end) as s3,
Min(case season when 4 then Rnk end) as s4

from
(select *,dense_rank() over (partition by Units.Item order by Units.unitsSold DESC) AS Rnk
from
UnitsSoldbyWeek Units,
Season sea

where
sea.Period = Units.period and
sea.Week = Units.week and
Units.Year = 11 and
Units.Item = abc
...
)t
GROUP BY Item
[/code]

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

dversloot
Starting Member

3 Posts

Posted - 2012-08-02 : 08:51:58

I am trying to rank the columns.
So if my output for Item ABC is currently:

Item S1 S2 S3 S4
ABC 4 6 1 3

I'm trying to get:

Item S1 S2 S3 S4 S1Rank S2Rank S3Rank S4Rank
ABC 4 6 1 3 2 1 4 3

In Excel I would use rank(4 from S1,S1:S4 Units)

quote:
Originally posted by Lamprey

Are you trying to rank the COLUMNS or the ROWS? I assume rows, but your sample only has one row, so maybe that assumption is incorrect. Can you show more sample data or explaine in words how you expect to rank, whatever you are trying rank?

Dan Versloot
Business Analyst

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 09:38:56

Try my suggestion. I think it should give you want you're looking at

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources