CODEWITHSUNDEEP
sqlsql-server
user642378
user642378

Reputation: 167

sum of rows using sqlserver

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.

Upvotes: 1

Views: 130

Answers (4)

LNRao
LNRao

Reputation: 169

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

Upvotes: 1

Lord Peter
Lord Peter

Reputation: 3501

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.

Upvotes: 1

Kaf
Kaf

Reputation: 33809

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

Upvotes: 3

xlecoustillier
xlecoustillier

Reputation: 16351

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)

Upvotes: 1

Related Questions