Menu
I am developing a small stored procedure on SQL server 2008. I have little knowledge on SQL queries, but enough to achieve simple tasks. However I came up with a problem I can't solve myself. Before I start explaining my problem, please pardon me if I spell a SQL query-word wrong because I am not a native English speaker.
I have 4 fields(CSV representation):
ID, NAME, VALUES, ANSWER
25, Tom , 2400 , 0
25, Tom , 600 , 0
25, Tom , 500 , 1
25, Tom , 300 , 1
27, Jerry, 100, 0
27, Jerry, 20, 1
27, Jerry, 60, 1
27, Jerry, 2000, 0
What I want to do is group by the selection by its ID and NAME, Sum up it's values in a field named positive when ANSWER = 1 and negative when ANSWER = 0.
ID, NAME, SUM, NEGATIVE, POSITIVE
25, Tom, 3000, 800
27, Jerry, 2100, 80
I Guess my question has been asked several times, but I wasn't able to find anything about it, probably because I am using the wrong terms. Anyway if someone could help that would save me a lot of time.
227
(1) Select the column name that you will sum based on, and then click the Primary Key button; (2) Select the column name that you will sum, and then click the Calculate > Sum. (3) Click the Ok button.
Enter this formula: =IF(A3<>A2,SUM($B$2:B2)-SUM($C$1:C1),"") into a blank cell beside your data range that you want to sum, C2, for example, and then drag this formula down to the cells that you want to get the results, and the cells in column B have been added together based on the value changes in column A.
If you need to sum numbers based on multiple criteria, you can use the SUMIFS function. The first range (D4:D11) are the cells to sum, called the "sum range". Criteria are supplied in pairs... (range / criteria).
You'll do so with a CASE statement.
select Id
, Name
, SUM(case when answer = 1 then Values else 0 end) as Positive
, SUM(case when answer = 0 then Values else 0 end) as Negative
from MyTable
group by Id
, Name
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
