Calculate Data In Groups

[bmacbmac]

Hi,

I have a table with the following fields;
BANK, MARKET, AMOUNT, TRANSACTIONS, TRANSPERCENTAGE

I would like to calculate the the percentage for each market, grouped by bank.

For example,

create table #temp3 (bank varchar(75), market varchar(50), amount money, Transactions decimal(18,2), TransPercentage  decimal(4,2))
go

insert into #temp3 values('AIMBANK', 'OTHER', 720712.5000, 3.00, NULL)
insert into #temp3 values('AIMBANK', 'SERVICE TITLE', 854827.000, 6.00, NULL)
insert into #temp3 values('AIMBANK', 'TITLE ONE', 172000.5000, 3.00, NULL)
insert into #temp3 values('AIMBANK', 'WESTERN TITLE', 439000.0000, 5.00, NULL)
insert into #temp3 values('BANK OF AMERICA', 'OTHER', 200500.0000, 2.00, NULL)
insert into #temp3 values('BANK OF AMERICA', 'SERVICE TITLE', 130500.0000, 1.00, NULL)
insert into #temp3 values('BANK OF AMERICA', 'WESTERN', 569000.0000, 3.00, NULL)

I would like to know, for the AIMBANK group, what the percentage of transactions are for each market. Then for the BANK OF AMERICA group, the percentage of transactions for each market in that group.

Any way to do this in SQL 2000?

Thanks in advance!

 

[r937]

SELECT t.bank
     , t.market
     , 100.0 * t.transactions / m.mkt_trans AS pct
  FROM ( SELECT market
              , SUM(transactions) as mkt_trans
           FROM daTable
         GROUP
             BY market ) AS m
INNER
  JOIN daTable AS t
    ON t.market = m.market

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bmacbmac]

Thanks R937! That appears to work perfectly. One follow up question.. would it be possible to update the actual TransPercentage field with this data? I've tried a few updates statements, but just cant make it work.

 

[markros]

Using SQL Server 2005 and up:

;with cte as (SELECT t.bank
     , t.market
     , 100.0 * t.transactions / 
NULLIF(SUM(Transactions) OVER (partition by t.Market),0) AS pct from #Temp3)

update cte set TransPercentage = pct

PluralSight Learning Library

 

[bmacbmac]

Thanks Markros.. I mentioned in the original post that I am using SQL 2000. Do you know of a way I can do it in 2000 to update my table?

 

[markros]

Sorry, missed it. Try instead:

Update T
set T.Percentage = 100.0 * t.transactions / NULLIF(m.mkt_trans,0)
  FROM daTable T INNER JOIN ( SELECT market
              , SUM(transactions) as mkt_trans
           FROM daTable
         GROUP
             BY market ) AS m
    ON t.market = m.market

PluralSight Learning Library

 

[bmacbmac]

perfect thanks!