创建/附加表，其中包含按不同类别分组的值的总和

2021-11-16 00:00:00 append sql-server-2008 sql-server

我有一个费用表，例如:

I have an expense table like:

WorkWeek  Catg  Item    Cost
WorkWeek1 Cat1  Item1   Price
WorkWeek1 Cat1  Item2   Price
WorkWeek1 Cat1  Item3   Price
WorkWeek1 Cat1  Item4   Price
WorkWeek1 Cat2  Item1   Price
WorkWeek1 Cat2  Item5   Price
WorkWeek1 Cat2  Item6   Price
WorkWeek1 Cat3  Item1   Price
WorkWeek1 Cat3  Item5   Price
.
.
WorkWeekA CatB  ItemC   Price

这就是我现在的做法:

select top(1)
     (select sum(cost) from DataTable where Catg like 'Cat1') as Cat1TotalCost
    ,(select sum(cost) from DataTable where Catg like 'Cat2') as Cat2TotalCost
    ,(select sum(cost) from DataTable where Catg like 'Cat3') as Cat3TotalCost
    .
    .
    .
    .
from DataTable where WorkWeek like 'WorkWeek1'

如果我不使用 top 1 那么我会得到相同的总和，就像数千行一样重复.此外，我的做法只占 1 个工作周.:(

And If I don't use the top 1 then I get the same sums repeated over like thousands of rows. Also, my way of doing it only accounts for 1 workweek. :(

我想创建一个表格，其中每个工作周的总费用取决于每个类别，例如:

I want to create a Table with each workweeks total expense depending in each category something like :

WorkWeek1   Cat1TotalCost   Cat2TotalCost   Cat3TotalCost
WorkWeek2   Cat1TotalCost   Cat2TotalCost   Cat3TotalCost
.
.

推荐答案

试试这个:

select
    workweek
    ,(select sum(cost) from DataTable where Catg = 'Cat1') as Cat1TotalCost
    ,(select sum(cost) from DataTable where Catg = 'Cat2') as Cat2TotalCost
    ,(select sum(cost) from DataTable where Catg = 'Cat3') as Cat3TotalCost
    .
    .
    .
    .
from DataTable
group by Workweek

现在，您正在按工作周字段分组.此外，我将 like 更改为 = 以使其稍微快一些.

Now, you are grouping by the workweek field. Also, I changed the like to = to make it slightly faster.

相关文章