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:

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!

CodePudding user response：

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 

CodePudding user response：

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

CodePudding user response：

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