I would like to develop a query in SQL Server 2008 that will fetch me the solution for “Month—Collection Sum” from a three columned table.
The columns are ID, Amount Collected and Date. For eg.,
Jan 2011 Collection sum should be the sum of all the entries in column Amount Collected dated on or before Jan 2011.
How can I make this happen? Please help.
SQL Server provides many built-in functions that you can use in queries to return data or perform operations on data.
There are various SQL built-in functions that can assist a developer or user to perform queries in a database.
The functions give you the ability to perform most of the statistical analysis on numeric values in a table using criteria from the same column or even a different column. The built-in functions are divided into four categories: Rowset functions, aggregate functions, ranking functions and Scalar functions.
The function that you can use to perform the summation falls under aggregate functions. These functions operate on several values but only return a single and summarised value.
The function that you will use in this case is SUM() and the structure for the function is:
SUM ( [ ALL | DISTINCT ] expression )
To develop a query for your table, the query will be as shown below:
USE YourDatabaseName;
GO
SELECT SUM(AmountCollected)
FROM YourTableName
WHERE Date < = YEAR(2011), MONTH(JANUARY);
GO
