I have a table like below and I would like to show TotalSales based on Category column.

I'm trying to use this query: select Category,sales,TotalSales from ( select Category,Sales, case when Category='Office Supplies' then sum(sales) when Category='Furniture' then sum(sales) end as TotalSales from Orders$ group by Category,Sales ) as tmp order by Category desc

I would like to show as below.

can anyone help to get this query.

Note: Percentage= Sales/Sub-Cate Total Sales

CodePudding user response：

If i understand correctly you need this, hope it helps :

Let's assume that your table name is TableName.

Select tn.Category,
       tn.Sub_Cate,
       tn.Sales,
       tn1.TotalSales,
       ((tn.Sales * 100) / tn1.TotalSales) PercOfTotal
  from TableName Tn
  join (Select category,
               sum(Sales) TotalSales
          from TableName Tn1      
               ) tn1 on tn1.category = tn.category
 order by 1,2,3

CodePudding user response：

In the immortal words of Tom Kyte, the Oracle grandmaster who launched "Ask Tom", "analytics rock, analytics roll."

Analytics is another name for window functions. What the windows function gives you is the ability to compute some aggregate function like sum() but make it available to every row in the group.

create table sales(category varchar,subcate varchar,sales float);
insert into sales values ('Chairs','Paper',16.448);
insert into sales values ('Chairs','Art',85);
insert into sales values('Suppliers','Binders',3.54);
insert into sales values('Suppliers','Binders',45.89);
insert into sales values('Furniture','Paper',75.235);

Now according to your question we need the total sales for each category and use it to create a percentage for each row (different subcate). The sum() window function works perfectly for that. The "partition by" column or columns if you need more than one, are the columns you would group by in order to get your sum, but doing it this way gives you that sum associated for each row

select 
    category,
    subcate,sales,
    sum(sales) over (partition by category) totalSales,
    100*sales/sum(sales) over (partition by category) pct 
from sales;