Sql Sum Multiple Rows into One

SQL Sum Multiple rows into one

Thank you for your responses. Turns out my problem was a database issue with duplicate entries, not with my logic. A quick table sync fixed that and the SUM feature worked as expected. This is all still useful knowledge for the SUM feature and is worth reading if you are having trouble using it.

SQL Server : SUM() of multiple rows including where clauses

This will bring back totals per property and type

SELECT  PropertyID,
        TYPE,
        SUM(Amount)
FROM    yourTable
GROUP BY    PropertyID,
            TYPE

This will bring back only active values

SELECT  PropertyID,
        TYPE,
        SUM(Amount)
FROM    yourTable
WHERE   EndDate IS NULL
GROUP BY    PropertyID,
            TYPE

and this will bring back totals for properties

SELECT  PropertyID,
        SUM(Amount)
FROM    yourTable
WHERE   EndDate IS NULL
GROUP BY    PropertyID

......

How to sum multiple rows in SQL which has different values in the adjacent column

If you are doing only LITERS and GALLONS then a conditional aggregation should to the trick

Example or dbFiddle

Select fuel
      ,units = 'gallon'
      ,total = sum( case when units='liter' then total * 0.264172 else total end )
 From  YourTable
 Group By fuel

Results

fuel    units   total
cng     gallon  50.000000
diesel  gallon  32.641720
lpg     gallon  20.000000

EDIT JUST FOR FUN. Let's add Barrels as well

Declare @YourTable Table ([fuel] varchar(50),[units] varchar(50),[total] int)
Insert Into @YourTable Values 
 ('diesel','gallon',30)
,('lpg','gallon',20)
,('cng','gallon',50)
,('diesel','liter',10)
,('diesel','barrel',1)
 
Select fuel
      ,units = 'gallon'
      ,total = sum( case units when 'liter' then 0.264172 
                               when 'barrel' then 42
                         else 1 
                     end * Total )
 From  @YourTable
 Group By fuel

2nd Results

fuel    units   total
cng     gallon  50.000000
diesel  gallon  74.641720
lpg     gallon  20.000000

how to sum multiple rows with same id in SQL Server

Simple GROUP BY and SUM should work.

SELECT ID, NAME, SUM([NO]) 
FROM Your_TableName
GROUP BY ID, NAME;

How to get sum of multiple rows in a table dynamically

You need to form the query dynamically and then execute it using sp_executesql or exec()

Note : char(9) is tab, char(13) is carriage return. These are added to format the query so that it is readable when you print it out for verification.

declare @sql nvarchar(max);

select @sql = 'with cte as (' + char(13)
            + 'select' + char(13)
            + string_agg(char(9) + quotename(column_name) + ' = sum(' + quotename(column_name) + ')', ',' + char(13)) + char(13)
            + 'from ' + max(quotename(table_name)) + char(13)
            + ')' + char(13)
            + 'select a.table_name, a.column_name, a.total_sum ' + char(13)
            + 'from   cte ' + char(13)
            + 'cross apply (' + char(13)
            + char(9) + 'values' + char(13)
            + string_agg(char(9) + '(''' + table_name + ''', ''' + column_name + ''',' + quotename(column_name) + ')', ',' + char(13)) + char(13)
            + ') a (table_name, column_name, total_sum)'
from   information_schema.columns
where  table_name = 'table_1'
and    data_type  = 'money'

print @sql
exec sp_executesql @sql

For your sample table, the generated dynamic query is

with cte as (
select
    [column_a] = sum([column_a]),
    [column_b] = sum([column_b]),
    [column_c] = sum([column_c])
from [table_1]
)
select a.table_name, a.column_name, a.total_sum 
from   cte 
cross apply (
    values
    ('table_1', 'column_a',[column_a]),
    ('table_1', 'column_b',[column_b]),
    ('table_1', 'column_c',[column_c])
) a (table_name, column_name, total_sum)

EDIT
using a loop to iterate each table. Basically it execute above query for each of the table and insert the result into a temp table

see db<>fiddle demo

for earlier SQL Server version without string_agg(), use for xml path

select @sql  = 'with cte as (' + char(13)
             + 'select' + char(13)
             + stuff
               (
                  (
                      select ',' + quotename(COLUMN_NAME) + ' = sum(' + quotename(COLUMN_NAME) + ')'
                      from   INFORMATION_SCHEMA.COLUMNS
                      where  TABLE_NAME = @table
                      and    DATA_TYPE  = 'money'
                      for xml path('')
                  ), 
                  1, 1, ''
               ) + char(13)
            + 'from ' + max(quotename(@table)) + char(13)
            + ')' + char(13)
            + 'select a.table_name, a.column_name, a.total_sum ' + char(13)
            + 'from   cte ' + char(13)
            + 'cross apply (' + char(13)
            + char(9) + 'values' + char(13)
            + stuff
            (
              (
                  select    ',' + '(''' + TABLE_NAME + ''', ''' + COLUMN_NAME + ''',' + quotename(COLUMN_NAME) + ')'
                  from   INFORMATION_SCHEMA.COLUMNS
                  where  TABLE_NAME = @table
                  and    DATA_TYPE  = 'money'
                  for xml path('')
              ),
            1, 1, ''
            )
            + ') a (table_name, column_name, total_sum)' + char(13)

SQL Server: Sum values from multiple rows into one row

You are trying to sum a string (even if you cast it)
The query will only work if your ISNULL goes to a 0 or some numeric value

SELECT
id, SUM(CASE WHEN CAST(value AS FLOAT) = 0
THEN CAST([other_value] AS FLOAT)
ELSE ISNULL([value], 0) 
END) AS 'Value'
FROM table1
WHERE id = 1
GROUP BY id

---

Related Topics