This is my Table called "SAM"
ID | S_Date | S_MK | TID | Value |
===============================================
1 | 2012-12-11 | 1 | 112 | 23 |
2 | 2012-12-11 | 2 | 112 | 3 |
3 | 2012-12-11 | 1 | 113 | 22 |
4 | 2012-12-11 | 3 | 114 | 2 |
This should be my expected result: sum of column "Value" with the same T_ID:
S_Date | TID | sum(Value)|
===============================
2012-12-11 | 112 | 26 |
2012-12-11 | 113 | 22 |
2012-12-11 | 114 | 2 |
To sum rows with same ID, use the GROUP BY HAVING clause.
You can specify either ALL or DISTINCT modifier in the SUM() function. The DISTINCT modifier instructs the SUM() function to calculate the total of distinct values, which means the duplicates are eliminated. The ALL modifier allows the SUM() function to return the sum of all values including duplicates.
Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).
You have to use aggregate function "sum" for the sum of value column. Further more you should include all the column in group by clause that you used in select statement. Show activity on this post. This will return the same table by summing up the Value column of the same TID column.
select S_Date, TID, sum(Value)
from SAM
group by S_Date, TID
If you really need this result set, with grouping only by T_ID, you can use this query:
SELECT (SELECT top 1 S_Date FROM SAM as t1 WHERE t1.TID = t2.TID) as S_Date,
TID,
SUM(Value)
FROM SAM as t2
GROUP BY TID
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