Get region sales along with state in SQL Server [closed]

e5njpo68 于 12个月前发布在 SQL Server

关注(0)|答案(1)|浏览(118)

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

I am new to SQL Server. I have a table with columns Region , State and Sales - like this:

I need to show region-wise sales along with state-wise sales in the same query.

Could any one please help how to do this?

1条答案

You can use rollup with group by clause like below to get region wise, state wise and total sales summary:

Query:

select region, state,sum(sales) [total sale]
from SalesData
group by region,state
  with rollup
order by region,state

Output:

Or you can use window function sum()over() to get the region-wise sales like below:

Query:

select region,sum(sales)over(partition by region)[region wise total sale],state,sales
from SalesData

Output:

region	region wise total sale	state	sales
East	300	California	100
East	300	Texas	200
West	300	Roseland	100
West	300	NewJersey	200