Bpangle

asked on

# How do I sum a value and then rank that value based on who sold it

Hello experts,

I am writing my first transact sql statement and need help with a query.

In my table I have a Whse, SalesID, Class and QUANTITY. The whse is repeated in there several times depending on what the Class value is and SalesID is and the quantity is based on what whse sold it and who sold it. What I am trying to do is get a sum of the quantity sold for each whse and then rank them accordingly.

I am able to pull back the sum easily enough and get a single row for each whse until I add the Rank() Over function then my summing no longer seems to work. I should be pulling back 52 rows and instead I'm getting 1565 rows. What it seems to be doing is going after the distinct sum value and why I say that is I tested this against a whse which had 4 records, record 1 shows qty of 18, record 2 shows qty of 14 and then there were 2 records which had a qty of 0 on them and it only returned 1 row for that record.

I would like to return a row for each whse with the sum of the qty sold and then rank that whse based on the qty sold.

I'm sure I probably have something wrong in my select statement and would appreciate any help you can give me.

I have attached a code snippet of what I am trying to do.

Thanks in advance for your help! bp

I am writing my first transact sql statement and need help with a query.

In my table I have a Whse, SalesID, Class and QUANTITY. The whse is repeated in there several times depending on what the Class value is and SalesID is and the quantity is based on what whse sold it and who sold it. What I am trying to do is get a sum of the quantity sold for each whse and then rank them accordingly.

I am able to pull back the sum easily enough and get a single row for each whse until I add the Rank() Over function then my summing no longer seems to work. I should be pulling back 52 rows and instead I'm getting 1565 rows. What it seems to be doing is going after the distinct sum value and why I say that is I tested this against a whse which had 4 records, record 1 shows qty of 18, record 2 shows qty of 14 and then there were 2 records which had a qty of 0 on them and it only returned 1 row for that record.

I would like to return a row for each whse with the sum of the qty sold and then rank that whse based on the qty sold.

I'm sure I probably have something wrong in my select statement and would appreciate any help you can give me.

I have attached a code snippet of what I am trying to do.

Thanks in advance for your help! bp

```
SELECT SUM(YTDQ) AS CURYTD, WHSE, RANK() OVER (PARTITION BY YTDQ ORDER BY WHSE DESC) AS 'RANK'
FROM LS_ALRO400.ALRO400.PCX.SAPOSUM
WHERE (CLASS IN ('002'))
GROUP BY WHSE, YTDQ
ORDER BY WHSE
```

ASKER

I'm still returning over 1500 rows but it looks like it's ranking them better

If you want to set criteria on the ranking:

select * from

(

select *, myranking = RANK() OVER (PARTITION BY CURYTD ORDER BY WHSE DESC) AS 'RANK'

from

(

SELECT SUM(YTDQ) AS CURYTD, WHSE,

FROM LS_ALRO400.ALRO400.PCX.SAPOSUM

WHERE (CLASS IN ('002'))

GROUP BY WHSE, YTDQ

) a

) b where myranking = 1

select * from

(

select *, myranking = RANK() OVER (PARTITION BY CURYTD ORDER BY WHSE DESC) AS 'RANK'

from

(

SELECT SUM(YTDQ) AS CURYTD, WHSE,

FROM LS_ALRO400.ALRO400.PCX.SAP

WHERE (CLASS IN ('002'))

GROUP BY WHSE, YTDQ

) a

) b where myranking = 1

ASKER

Ok, I'm not understanding your code I'm getting syntax errors when I try to run this. Bear with me because I have never done a transact SQL before and much of this is greek to me.

I'm not having a problem getting a rank value of the qty what I'm having trouble with is getting the SQL to return only 1 row value for each whse. If I run this query

Select sum(YTDQ) as curytd, whse from 'some table' where class = '002' group by whse

I return a sum value for curytd for each individual whse

When I add the rank statement above I no longer get an individual record for each whse instead I get an individual summary record for each YTDQ value which is not what I want.

Hope that makes sense.

I'm not having a problem getting a rank value of the qty what I'm having trouble with is getting the SQL to return only 1 row value for each whse. If I run this query

Select sum(YTDQ) as curytd, whse from 'some table' where class = '002' group by whse

I return a sum value for curytd for each individual whse

When I add the rank statement above I no longer get an individual record for each whse instead I get an individual summary record for each YTDQ value which is not what I want.

Hope that makes sense.

Ok...give me some sample data from your table and what you want your results to be....

ASKER

Ok, here you go. Table 1 shows what is in the table now

Table 1

QTY WHSE

956 A

600 A

300 A

258 B

50 B

50 B

5000 C

Table 2 is what I want to return

QTY WHSE RANK

1856 A 2

358 B 3

5000 C 1

Table 3 is what I'm getting instead

QTY WHSE RANK

956 A 1

600 A 2

300 A 3

400 B 1

300 B 2

50 B 3 (This one actually had 2 qty values of 50 for the same whse)

5000 C 1

Table 1

QTY WHSE

956 A

600 A

300 A

258 B

50 B

50 B

5000 C

Table 2 is what I want to return

QTY WHSE RANK

1856 A 2

358 B 3

5000 C 1

Table 3 is what I'm getting instead

QTY WHSE RANK

956 A 1

600 A 2

300 A 3

400 B 1

300 B 2

50 B 3 (This one actually had 2 qty values of 50 for the same whse)

5000 C 1

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

Oops sorry I had something wrong on table 3 here is the corrected one.

QTY WHSE RANK

956 A 1

600 A 2

300 A 3

258 B 1

50 B 2 (This one actually had 2 qty values of 50 for the same whse)

5000 C 1

QTY WHSE RANK

956 A 1

600 A 2

300 A 3

258 B 1

50 B 2 (This one actually had 2 qty values of 50 for the same whse)

5000 C 1

ASKER

You are so good! Thank you so much that was perfect. I'll get the points awarded.

Geezz!!! Just kidding. :)

This works for that:

select whse, qty , ranking = dense_rank() over(partition by whse order by qty desc)

from #t

This works for that:

select whse, qty , ranking = dense_rank() over(partition by whse order by qty desc)

from #t

ASKER

I truly appreciate the quick response and patience you awarded to me with this solution. I am a beginner but am trying hard to search before I ask so this was much appreciated!

select *, RANK() OVER (PARTITION BY CURYTD ORDER BY WHSE DESC) AS 'RANK'

from

(

SELECT SUM(YTDQ) AS CURYTD, WHSE,

FROM LS_ALRO400.ALRO400.PCX.SAP

WHERE (CLASS IN ('002'))

GROUP BY WHSE, YTDQ

) a