Link to home
Create AccountLog in
Avatar of Bpangle
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
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

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

Hi...try this:

select *, 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
Avatar of Bpangle
Bpangle

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
Avatar of Bpangle

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.
Ok...give me some sample data from your table and what you want your results to be....
Avatar of Bpangle

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
 
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Bpangle

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
Avatar of Bpangle

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
Avatar of Bpangle

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!