CODEWITHSUNDEEP
sqloracle-database
Matěj Alex
Matěj Alex

Reputation: 3

SQL making column with % of total

I am making a table with amount of transactions from other banks. First row will be the total one. First column with absolute numbers, second with % of the total amount and third, last column, will be with name of the senders bank.

eg:

TRN % BANK
8000 100% ALL
4000 50% BANK1
2000 25% BANK2
2000 25% BANK3

I have trouble getting the % column as in my script the data are groupped already.

SELECT COUNT(*)
      ,'100%' as %,
      ,'ALL' as BANK
FROM table A

UNION ALL 

SELECT COUNT(*) 
      ,**IDK**
      ,BANK_ID BANK
FROM TABLE2 B
GROUP BY A.BANK_ID
ORDER BY 1 DESC

I know that there should be possibility with the over partition, but I can't figure out how.

Thanks for any tips!

Upvotes: 0

Views: 80

Answers (5)

d r
d r

Reputation: 7801

The MODEL clause could be used for this:

--  S a m p l e   d a t a
WITH
    tbl (BANK_ID, TRN) AS
        (
            Select  'Bank 1',  500 From Dual Union All
            Select  'Bank 1', 3500 From Dual Union All 
            Select  'Bank 2', 1200 From Dual Union All 
            Select  'Bank 2',  800 From Dual Union All 
            Select  'Bank 3', 2000 From Dual 
        )

--  M a i n   S Q L
SELECT  TRN, PCT, BANK_ID
FROM  ( SELECT    BANK_ID "BANK_ID", Sum(TRN) "TRN"
        FROM      tbl
        GROUP BY  BANK_ID
        ORDER BY  BANK_ID
      )
      MODEL   Dimension By(BANK_ID )
              Measures(TRN, 0 as PCT)
              RULES 
              (   TRN['ALL'] = Sum(TRN)[BANK_ID != 'ALL'],
                  TRN[ANY] = Sum(TRN)[CV()],
                  PCT['ALL'] = 100,
                  PCT[ANY] = Sum(TRN)[CV()] * 100 / Sum(TRN)[BANK_ID != 'ALL']
              )
ORDER BY BANK_ID    
--  R e s u l t
       TRN        PCT BANK_ID
---------- ---------- -------
      8000        100 ALL     
      4000         50 Bank 1  
      2000         25 Bank 2  
      2000         25 Bank 3

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

Sample data:

create table demo (trn number, bank_id varchar2(10));

insert all
    into demo values (1000, 'BANK1')
    into demo values (1000, 'BANK1')
    into demo values (1000, 'BANK1')
    into demo values (1000, 'BANK1')
    into demo values (1000, 'BANK2')
    into demo values (1000, 'BANK2')
    into demo values (1000, 'BANK3')
    into demo values (1000, 'BANK3')
select * from dual;

Query:

select sum(trn)
     , 100 * ratio_to_report(sum(trn)) over () * 2 as percent
     , nvl(bank_id,'ALL') as bank
from   demo
group by rollup (bank_id);

  SUM(TRN)    PERCENT BANK
---------- ---------- ----------
      4000         50 BANK1
      2000         25 BANK2
      2000         25 BANK3
      8000        100 ALL

group by rollup() generates a total row.

ratio_to_report() returns a value between 0 and 1, so I multiply by 100 to present it as a percentage.

The * 2 is because ratio_to_report() includes the whole column including the rollup total, so every value is halved.

I've assumed bank_id cannot be null and so a null value in the report must be the total row. If you need to distinguish between the total row and other null values, you can use grouping(bank_id) in a case expression, and it will return 1 for the total row and 0 for the rest. (You could also use this in an order by clause if you want to display the total row first.)

Upvotes: 1

Matěj Alex
Matěj Alex

Reputation: 3

I got to this solution and it works thankfully,

with alll as
(
SELECT
COUNT(*) trn

,'100%' AS prcnt
,'ALL' AS BANK_ID

FROM table

)
, bank as 
(
SELECT distinct


count(1) over (partition by BANK_ID) cnt_B
,to_char(round(count(1) over (partition by BANK_ID)/count(*) over (partition by 1),3)*100) || '%' as prcnt
,BANK_ID

FROM table
)



select * from alll

UNION ALL 

select * from bank

Upvotes: 0

ClearlyClueless
ClearlyClueless

Reputation: 762

I am not overly familiar with Oracle syntax so the below may need some adjustments, but it should be portable enough. This is in MS SQL. Apologies for not being able to provide you a solution in the direct syntax you need. If someone is more familiar, please feel free to edit.

DECLARE @totalCount BIGINT = (SELECT Count(*) FROM table);
SELECT 
    @TotalCount AS [Count],
    FORMAT(1, 'P') AS [%],
    'ALL' AS [BANK]
--removed table here as the only derived value is the count and we already have it in @totalCount

UNION ALL

SELECT 
    COUNT(*) AS [Count], 
    FORMAT(CAST(Count(*) AS DECIMAL) / @TotalCount,'P') AS [%],
    BANK_ID AS [BANK]
FROM [tableName]
GROUP BY [tableName].BANK_ID
--ORDER BY 1

Upvotes: 0

bradjlund
bradjlund

Reputation: 11

Hi I think this should do the trick.

You can use the over partition with specifying anything in the brackets.

SELECT COUNT(*)
      ,'100%' as '%',
      ,'ALL' as BANK

UNION ALL 

SELECT COUNT(*) 
      ,cast(count(*)*100/count(*) over () as varchar) +'%'
      ,BANK_ID BANK
FROM TABLE2 B
GROUP BY B.BANK_ID
ORDER BY 1 DESC

Upvotes: 1

Related Questions