Year to Date Totals in SQL

[pringb]

Hi there,

I am trying to calculate via SQL a running YTD total on a summary tabl
based on month and type. Any help would be appreciated.

Table with YTD as I would like it to appear.

Month,type,calc1,ytd

1,a,10,10
1,b,12,12
2,a,10,20
2,b,15,27
3,a,5,25
3,b,3,28

Cheers

Bruc

 

[Brian Camire]

You might try a query whose SQL look something like this:

SELECT
[Your Table].[Month],
[Your Table].[type],
[Your Table].[calc1],
Sum([Self].[calc1]) AS [ytd]
FROM
[Your Table]
INNER JOIN
[Your Table] AS [Self]
ON
[Your Table].[type] = [Self].[type]
WHERE
[Self].[Month] <= [Your Table].[Month]
GROUP BY
[Your Table].[Month],
[Your Table].[type],
[Your Table].[calc1]

Another approach might be:

SELECT
[Your Table].[Month],
[Your Table].[type],
[Your Table].[calc1],
(SELECT
Sum([Self].[calc1])
FROM
[Your Table] AS [Self].
WHERE
[Self].[type] = [Your Table].[type]
AND
[Self].[Month] <= [Your Table].[Month]) AS [ytd]

 

[pringb]

thanks, that's fantastic !