Menu
i have an table with values like this:
count1 count2 count3 month
12 1 4 01/12/2011
6 5 4 23/12/2011
14 6 9 11/06/2011
8 5 4 19/06/2011
How do I get the following results?
count1 count2 count3 month
18 6 8 12
22 11 13 06
819
You can also find out the total count of sales every month. For that, replace the SUM function with the COUNT function. Query: SELECT YEAR(Order_date) AS Year,MONTH(Order_date) AS Month,COUNT(Sales) AS Count_Of_Sales FROM Products GROUP BY YEAR(Order_date),MONTH(Order_date);
Cumulative Sum in SQL Server : In SQL server also you can calculate cumulative sum by using sum function. We can use same table as sample table. select dept_no Department_no, count(empno) Employee_Per_Dept, sum(count(*)) over (order by deptno) Cumulative_Total from [DBO].
In SQL, we use the SUM() function to add the numeric values in a column. It is an aggregate function in SQL. The aggregate function is used in conjunction with the WHERE clause to extract more information from the data.
Discussion: Use ORDER BY if you want to order rows according to a value returned by an aggregate function like SUM() . The ORDER BY operator is followed by the aggregate function (in our example, SUM() ). DESC is placed after this function to specify a descending sort order.
SELECT SUM(count1), SUM(count2), SUM(count3), MONTH(month)
FROM myTable
GROUP BY MONTH(month)
Since MONTH is an SQL Server keyword, you might have to escape the column name month (e.g. [month]), if you date column is really called like that. (Thanks Endy for that comment!)
Also, if data selected span more than one year, you will probably need to group by year as well:
SELECT SUM(count1), SUM(count2), SUM(count3), MONTH(month), YEAR(month)
FROM myTable
GROUP BY MONTH(month), YEAR(month)
reduce the query burden
SELECT SUM(count1), SUM(count2), SUM(count3), substring(month,4,2)
FROM myTable
GROUP BY substring(month,4,2)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
