sum of rows using sqlserver

Question

I am using sqlserver, which has following table ,

id    companyname     credited      used
----------------------------------------
1       ABC             100          0
2       ABC             10           0
3       BBB              0           10
4       BBB              0           10
5       BBB             100           0
6       BBB             10            0

My expected output as below

id    companyname     credited      used      sum_credited    sum_used
--------------------------------------------------------------------------
1       ABC             100          0         110              0
2       ABC             10           0         110              0
3       BBB              0           10         0               20
4       BBB              0           10         0               20
5       BBB             100           0        110              0
6       BBB             10            0        110              0

I have used the below query in which i didnt get as espected output

SELECT id,companyname,Credited,Used, SUM(credited) 'Sum_Credited'  
FROM tmptichistory1 
GROUP BY id,companyname,Credited,Used

I want to find the sum of credited and used values and display it in different columns with original values as i mentioned in the first table. Any help is appreciable.

Indi · Accepted Answer · 2012-12-21 11:39:39Z

3

sum group by companyname;

Mistake (thanks @bluefeet) corrected with a CASE here is fiddle:

;with cte as
(
  select companyname, sum(credited) sum_credited, sum(used) sum_used
  from tmptichistory1
  group by companyname
)
select t1.id, t1.companyname, t1.credited, t1.used, 
 case t1.credited when 0 then 0 else cte.sum_credited end sum_credited,
 case t1.used when 0 then 0 else cte.sum_used end sum_used
from tmptichistory1 t1 join cte
on t1.companyname = cte.companyname

edited Dec 21, 2012 at 11:39

answered Dec 21, 2012 at 11:17

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Taryn Over a year ago

I didn't downvote you but this doesn't give the desired output see this sql fiddle -- sqlfiddle.com/#!3/fb977/24 -- It puts the sum credited and sum used on each row which is not what the OP initially requested.

Indi Over a year ago

@bluefeet : thanks !!! I have rectified here

xlecoustillier · Accepted Answer · 2012-12-21 11:22:36Z

1

SELECT t1.id,
       t2.companyname,
       t1.credited,
       t1.used,
       t2.sum_credited,
       t2.sum_used
  FROM yourTable t1
  INNER JOIN
  (
      SELECT companyname, SUM( credited)  sum_credited , SUM(used) sum_used
        FROM yourTable 
       WHERE credited = 0
       GROUP BY companyname
      UNION
      SELECT companyname, SUM( credited)  sum_credited , SUM(used) sum_used
        FROM yourTable 
       WHERE used = 0
       GROUP BY companyname
  ) t2 ON t1.companyname = t2.companyname
  WHERE (t1.used = 0 AND t2.sum_used = 0) OR (t1.credited = 0 AND t2.sum_credited = 0)

edited Dec 21, 2012 at 11:22

answered Dec 21, 2012 at 11:15

xlecoustillier

16.4k15 gold badges64 silver badges88 bronze badges

Comments

Lord Peter · Accepted Answer · 2012-12-21 11:41:57Z

If you are using 2008 version or later, you can use analytic functions like this:

select 
id,
companyname,
credited,
used,
sum(credited) over (partition by companyname) sum_credited,
sum(used) over (partition by companyname) sum_used

from
tmptichistory1

But when I run it against your data I get the following output:

+----+-------------+----------+------+--------------+----------+
| id | companyname | credited | used | sum_credited | sum_used |
+----+-------------+----------+------+--------------+----------+
|  1 | ABC         |      100 |    0 |          110 |        0 |
|  2 | ABC         |       10 |    0 |          110 |        0 |
|  3 | BBB         |        0 |   10 |          110 |       20 |
|  4 | BBB         |        0 |   10 |          110 |       20 |
|  5 | BBB         |      100 |    0 |          110 |       20 |
|  6 | BBB         |       10 |    0 |          110 |       20 |
+----+-------------+----------+------+--------------+----------+

... so i think your rows with id = 5 and id = 6 should perhaps be for a different companyname? If we say companyname CCC then we get the following output:

+----+-------------+----------+------+--------------+----------+
| id | companyname | credited | used | sum_credited | sum_used |
+----+-------------+----------+------+--------------+----------+
|  1 | ABC         |      100 |    0 |          110 |        0 |
|  2 | ABC         |       10 |    0 |          110 |        0 |
|  3 | BBB         |        0 |   10 |            0 |       20 |
|  4 | BBB         |        0 |   10 |            0 |       20 |
|  5 | CCC         |      100 |    0 |          110 |        0 |
|  6 | CCC         |       10 |    0 |          110 |        0 |
+----+-------------+----------+------+--------------+----------+

Which looks more like what you have in your example.

Hans Keﬆing · Accepted Answer · 2012-12-21 11:42:01Z

1

Try this one it may help you..

select e1.Id,e1.CompanyNmae, e1.credited, e1.used,e2.SumCredited,e2.SumUsed  
from Example e1
inner join (select CompanyNmae, sum(credited) as SumCredited, sum(used) as SumUsed 
     from Example group by CompanyNmae) as e2
  on e1.CompanyNmae=e2.CompanyNmae

edited Dec 21, 2012 at 11:42

Hans Keﬆing

39.5k10 gold badges84 silver badges119 bronze badges

answered Dec 21, 2012 at 11:39

LNRao

1694 bronze badges

Collectives™ on Stack Overflow

sum of rows using sqlserver

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related