I am writing a sql query to get sales for different stores on a given day. The query is run against ingres/vectorwise. I want to add a column rank where there is the ranking of the store in regard of sales made in comparaison to all the stores.
My select statement is like follows:
SELECT store_number, sum(sales) as sales
FROM stores_sales_indicators
WHERE day = '2019-07-24'
GROUP BY store_number
I tried different things that I am familiar with from sql-server but none of it worked.
I think this is similar to what you're describing (no day included here but you'll get the idea):
declare global temporary table session.stores_sales_indicators
(
store_number integer not null,
sales integer not null
)
on commit preserve rows with norecovery, structure=x100;
insert into session.stores_sales_indicators
values(1,100),(1,200),(2,500),(2,50),(3,50),(3,300);
select
store_number,
sum(sales) as sales,
rank() over (order by sum(sales) desc) as rank
from session.stores_sales_indicators
group by store_number;
See also the fine manual, here's a link to the section on analytic functions: https://docs.actian.com/vector/5.1/index.html#page/SQLLang%2FAnalytical_Functions.htm