Create/append Table With Sum Of Values Grouped In Different Categories

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 W

Solution 1:

Try this:

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

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

Solution 2:

Looks like you have many categories, are you looking for a grouping ?

select worksheet, category, sum(cost) from DataTablegroup by worksheet, category orderby worksheet