Help with Sum Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Sum Function

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-12-22 : 12:19:11

I am trying to add a column called "company total" to the below query:

select c.symbol, hc.holding_company_name, SUM(CAST(bi.volume AS bigint)) AS vol from holding_company hc
inner join company c on c.holding_company_id = hc.holding_company_id
inner join invoices bi on bi.company_id = c.company_id
group by hc.holding_company_id, c.symbol, hc.holding_company_name
order by hc.holding_company_name

This new column will create a total based on the volumes from a same holding_company_name.

Below is sample data (including how I would want the new column to look):

symbol holding_company_name vol new column
AA Acadia Life 1000 1000
BB ACE Group 2000 2000
CC AEGON USA Inc 4000 15000
DD AEGON USA Inc 5000 15000
EE AEGON USA Inc 6000 15000

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 12:48:36

Try this..

;WITH Base_query (symbol,holding_company_name,vol)
AS
(
select c.symbol, hc.holding_company_name, SUM(CAST(bi.volume AS bigint)) AS vol from holding_company hc
inner join company c on c.holding_company_id = hc.holding_company_id
inner join invoices bi on bi.company_id = c.company_id
group by hc.holding_company_id, c.symbol, hc.holding_company_name
order by hc.holding_company_name
)

select a.symbol,a.holding_company_name,a.vol,t.new_column
from Base_query a cross apply
(
select holding_company_name,sum(vol) as new_column from Base_query
where holding_company_name = a.holding_company_name
group by holding_company_name
) t

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-22 : 12:51:57

select c.symbol, hc.holding_company_name, SUM(CAST(bi.volume AS bigint)) AS vol, a.vol as Total_Volume from holding_company hc
inner join company c on c.holding_company_id = hc.holding_company_id
inner join invoices bi on bi.company_id = c.company_id
Inner Join (select hc.holding_company_name, SUM(CAST(bi.volume AS bigint)) AS vol from holding_company hc
inner join invoices bi on bi.company_id = c.company_id
group by hc.holding_company_id, hc.holding_company_name) a on a.holding_company_name = hc.holding_company_name
group by hc.holding_company_id, c.symbol, hc.holding_company_name
order by hc.holding_company_name

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-22 : 12:52:39

DECLARE @Table Table (Symbol char(2), Company varchar(50),Vol int)
INSERT INTO @table
SELECT 'AA','Acadia Life', 1000 UNION ALL --1000
SELECT 'BB','ACE Group', 2000 UNION ALL --2000
SELECT 'CC','AEGON USA Inc', 4000 UNION ALL --15000
SELECT 'DD','AEGON USA Inc', 5000 UNION ALL --15000
SELECT 'EE','AEGON USA Inc', 6000 -- 15000

select symbol,company,vol,[NewAmount] = SUM (Vol) Over (partition BY Company) from @table

Jim

Everyday I learn something that somebody else already knew

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 12:54:58

quote:
Originally posted by jimf

DECLARE @Table Table (Symbol char(2), Company varchar(50),Vol int)
INSERT INTO @table
SELECT 'AA','Acadia Life', 1000 UNION ALL --1000
SELECT 'BB','ACE Group', 2000 UNION ALL --2000
SELECT 'CC','AEGON USA Inc', 4000 UNION ALL --15000
SELECT 'DD','AEGON USA Inc', 5000 UNION ALL --15000
SELECT 'EE','AEGON USA Inc', 6000 -- 15000

select symbol,company,vol,[NewAmount] = SUM (Vol) Over (partition BY Company) from @table

Jim

Everyday I learn something that somebody else already knew