How to Sum Multiple Lines in SQL

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 lines in sql

SELECT Company_Name, Company_ID, SUM(Amount) 
FROM TableName GROUP BY Company_Name, Company_ID

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;

Sum multiple rows while writing a query SQL

You group by b.partyID, b.credit, b.total, b.price. This means you get one result row per b.partyID, b.credit, b.total, b.price.

Let's say these are your table's records:

partyID  credit  price  total  date
1        10      20     30     2015-10-01
1        30      20     10     2015-10-02
1        10      20     30     2015-10-03
1        30      20     50     2015-10-04
2        10      20     30     2015-10-01

then you'll get (which you could also have got with DISTINCT, as you are not using any aggregate functions):

GROUP BY clause applied:

partyID  credit  price  total
1        10      20     30
1        30      20     10
1        30      20     50
2        10      20     30

SELECT clause applied:

partyID  opening  debit  credit  closing
1        10        20    0       30
1        30        0     20      10
1        30        20    0       50
2        10        20    0       30

In your SELECT clause you are using subqueries such as (select top 1 b.credit). So you are saying: "Give me one record. Of all these one records (sic) give me the top one by whatever order you like (TOP without ORDER BY). Fill this record with one value. This one value is to be b.credit."

b.credit is in the GROUP BY clause, so there is only one value per group. You can easily replace the whole subquery (select top 1 b.credit) with a mere b.credit.

Here is your query re-written:

SELECT distinct
  b.partyID, 
  b.credit as opening,
  case when b.total > b.credit then b.price else 0 end as debit,
  case when b.total < b.credit then b.price else 0 end as credit,
  b.total as closing
FROM tblPartyOrder b
WHERE b.date >= '2014-03-06' and b.date <= '2016-03-09'
ORDER BY b.partyID;

Now to your actual problem: You want one row per partyID, so group by partyID (only). You want a sum, so use the aggregate function SUM. You want to find the first and last record per partyID, so mark them somehow. You can use the analytic function ROW_NUMBER for this, giving these records the #1:

select
  partyid,
  min(case when first_is_one = 1 then credit end) as opening,
  sum(case when total > credit then price else 0 end) as debit,
  sum(case when total < credit then price else 0 end) as credit, 
  min(case when last_is_one = 1 then total end) as closing
from
(
  select
    po.partyid, po.credit, po.total, po.price,
    row_number() over (partition by po.partyid order by po.date) as first_is_one,
    row_number() over (partition by po.partyid order by po.date desc) as last_is_one
  from tblpartyorder po
  where po.date >= '2014-03-06' and po.date <= '2016-03-09'
) marked
group by partyid
order by partyid;

How to sum multiple rows into 1 group in laravel?

I found a way

$data = Clocks::groupBy('user_id')
        ->selectRaw('SEC_TO_TIME( SUM(time_to_sec(`total_time`)))  as total_time,
                            SUM(`earned_amount`) as earned_amount,
                            SUM(`bonus_pay`) as bonus_pay, user_id')
        ->get();

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)

---

Related Topics