SQL Rank () OVER Partition with mutliple where

Asked Feb 08 '17 at 09:20

Active Feb 08 '17 at 10:00

Viewed 498 times

I've got the database from which I would like to extract some sales data divided into clients and months. I've tried using Rank () over (partition by ...) but doesn't work.

SELECT * FROM (SELECT access_Month, client, 
  Sum(sales) as sales,
  RANK () OVER (PARTITION BY Month ORDER BY Sum(sales) DESC) as rank
  FROM DB.SALES
  WHERE (country='poland')AND (Year=2016)
  GROUP BY Month, client)
  WHERE rank<8

 I got results:
 Month  Client  Sales   Rank
 1       A      100,000  1
 1       B      10,000   2
 1       C       1,000   3
 1       D       100     4
 1       E        10     5
 1  F   1   6
 .          
 .          
 .          
 1  X   0   30
 2  A   120,000 1
 2  B   14,400  2
 2  C   1,728   3
 2  D   207 4
 2  E   25  5
 2       F  3   6
 .      0   
 .      0   
 .      0   
 2  X   0   30

When I add GROUP BY clause in the way as aforementioned there displays syntax rank error.

I got results:

My aim is limit numbers of displaying clients to 8

edited Feb 08 '17 at 10:00

asked Feb 08 '17 at 09:20

user7531072

share your sample db – Chintan Udeshi Feb 08 '17 at 09:21
1

and add the proper RDBMS tag... I guess SQL server ? – Thomas G Feb 08 '17 at 09:22
4

"doesnt work" is not a problem description. Why doesn't it work? Error? Unexpected results? What results do you expect? – HoneyBadger Feb 08 '17 at 09:26
1

You are aggregating without a `group by`. So without knowing what you are actually trying to do, to get rid of any errors (which you should be getting) you should add another subquery to do the summation, and do the ranking in an outer query. – HoneyBadger Feb 08 '17 at 09:30
I am trying to extract TOP N clients with the highest sales in every month of the year – user7531072 Feb 08 '17 at 09:39
I guess mySQL server – user7531072 Feb 08 '17 at 09:43
@HoneyBadger it's showing me syntax error – user7531072 Feb 08 '17 at 09:43
MySQL allows us to use aggregate without a group by not mssql or oracle – shv22 Feb 08 '17 at 09:46
If you get an error, you should show us the error. You say you use mySQL server, and tagged sql-server. Be aware sql-server and mySQL are different products. Can you tell us in plain words, and with sample and expected data, what you are trying to do? – HoneyBadger Feb 08 '17 at 09:46

SQL Rank () OVER Partition with mutliple where

0 Answers0