I have a stored procedure and it results like this:
Governors AUTO 07313570121 1 3.69 2.01 2.01 1.68 83.58%
Governors AUTO 07319354850 1 2.79 1.8 1.80 0.99 55.00%
Governors AUTO 07480400400 1 17.69 9.71 9.7117 7.9783 82.15%
Governors AUTO 07723100038 1 2.89 1.55 1.55 1.34 86.45%
Governors BEER 01820000031 6 4.69 23.34 3.888 0.8 20.57%
Governors BEER 01820000051 6 4.69 23.34 3.888 0.802 20.63%
Governors BEER 01820000106 1 6.39 4.93 4.93 1.46 29.61%
i want to sum like as fallows:
Governors AUTO 4 27.06 15.07
Governors AUTO 13 13.07 51.61
How do I sum the same records in SQL? 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.
To sum rows with same ID, use the GROUP BY HAVING clause.
SUM() and COUNT() functions SUM of values of a field or column of a SQL table, generated using SQL SUM() function can be stored in a variable or temporary column referred as alias. The same approach can be used with SQL COUNT() function too.
Coalesce() Function in SQL Server The "Coalesce()" function returns the first non-null value among its arguments. This function doesn't limit the number of arguments, but they must all be of the same data type. Now we use the ISNULL Function to convert a NULL value to 0 to find the sum of all columns.
without your column names it's a little bit tricky, but it would be something like:
CREATE TABLE #MyTable
(
col1 varchar(50),
col2 varchar(4),
col3 varchar(11),
col4 int,
col5 decimal(18,2),
col6 decimal(18,2),
col7 decimal(18,2),
col8 decimal(18,2),
col9 decimal(18,2) --might need to be varchar if the % sign comes back
)
insert into #MyTable
Exec storedProc
select col1, col2, sum(col4), sum(col5), sum(col7)
FROM #MyTable
GROUP BY col1, col2
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