A problem with GROUP BY in SQL Server?

Question

4.00/5 (3 votes)

See more:

Suppose I have a table (named MyIncome) with 3 columns, the first is Year (2008, 2008, 2009), the second is Month (1, 2, 1), the third is Income ($1000, $1500, $2000)
Now I want to know how much the Income are for each year in the Year column. I used the following T-SQL statement:
SELECT Year, sum(Income) as [Annual Income] FROM MyIncome GROUP BY Year
The result should be a table with two columns, one is Year(2008, 2009), the other is Annual Income ($2500,$2000).
But it didn't work unless I had to group by both Year and Income (this will cause the result to be different from what I want with grouping by Year only).
Especially the above statement seems to work well in Access, could you please give me a solution for my problem, I think it is very often to do such a job.
Thank you so much!
UPDATE
I'm sorry for the mistake, please see another problem of mine, now I want to know the income up to each year (for example, for the MyIncome table above, the result should be a table with two columns: Year(2008, 2009) and Total Income(2500, 4500)). The income up to 2009 is the sum of 2008's income and 2009's income.
Please help me solve that.
Thank you so much!

Posted 25-May-11 18:30pm

King Boy

Updated 25-May-11 20:01pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

arindamrudra · Accepted Answer · 2011-05-25T18:53:00

Solution 1

Can you have a look at the code given below:

USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail AS sod
GROUP BY SalesOrderID
ORDER BY SalesOrderID;

Link is: http://msdn.microsoft.com/en-us/library/ms177673.aspx[^]

Examples using group by clause.

Hope this will help you.

I tested your code:

SELECT [Year], sum(Income) as [Annual Income] FROM dbo.Table_1 GROUP BY [Year]

Out put is:
2008 2500
2009 2000

So no problem with it.

You are absolutely correct.

Updated Answer:

Please check the code given below:

SQL

DECLARE @TableVar TABLE
(
    YearWise        BIGINT,
    TotalAmount     BIGINT
)
INSERT INTO @TableVar
SELECT [Year], sum(Income) as [Annual Income] FROM dbo.Table_1 GROUP BY [Year]
SELECT YearWise,(SELECT SUM(TotalAmount)
FROM @TableVar
WHERE TotalAmount>=t.TotalAmount) AS CumulativeSum
FROM @TableVar t

Output:

YearWise CumulativeSum
2008 2500
2009 4500

It will work fine.
Hope this will help you.
Cheers man..

Posted 25-May-11 18:53pm

arindamrudra

Updated 25-May-11 20:48pm

v6

Comments

[no name] 26-May-11 2:08am

I'm sorry for the mistake about "The true problem", it seems that I was joking! Please see my updated question to help me solve another problem!
Thank you so much!

arindamrudra 26-May-11 2:35am

So you want the cumulative sum.

arindamrudra 26-May-11 2:46am

Check my updated answer.

arindamrudra 26-May-11 2:50am

Look for other solutions too. There may be another good solution for that.

arindamrudra 26-May-11 2:53am

Please check this link:
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/75477b69-41e5-4b49-8a13-c578a803c66d

[no name] 26-May-11 3:28am

Thank you!
I've found my own solution using only one SELECT, let's see:
SELECT v.Year, sum(Income) AS Total FROM (SELECT DISTINCT Year FROM MyIncome) AS v, MyIncome
WHERE MyIncome.Year <= v.Year
GROUP BY v.Year
Haha, it works well at all!!!
Thank you so much! My original problem was when I didn't use DISTINCT in the inner SELECT.
Thank you so much!

arindamrudra 26-May-11 3:34am

Great, I told you always there is a better solution.