How to use SUM and COUNT and add the results as a resulting column

Question

CREATE TABLE my_table ( bank_account, bank_id, amount ) AS
SELECT 123, 600, 1500 FROM DUAL UNION ALL
SELECT 123, 600, 2500 FROM DUAL UNION ALL
SELECT 123, 600, 3500 FROM DUAL UNION ALL
SELECT 123, 700,  500 FROM DUAL UNION ALL
SELECT 123, 700, 1000 FROM DUAL UNION ALL
SELECT 456, 800, 2000 FROM DUAL UNION ALL
SELECT 456, 900, 2000 FROM DUAL UNION ALL
SELECT 456, 900, 4000 FROM DUAL;

I need to write the SQL code where the result would look like this:

Where:
total_amount - the sum of all transactions bank_account made in specific bank_id
number_of_transactions - number of transactions made by bank_account in specific bank_id total_num_trans - total number of transactions made by bank_account
total_am_trans - total amount of transactions made by bank_account

I've only managed to get some of the results I need, but can't get them all. This is with what I've started:

  SELECT t.bank_account
       , t.bank_id
       , count(*)       number_of_transactions
       , sum(t.amount)  total_amount
    FROM my_table t 
GROUP BY t.bank_account
       , t.bank_id
ORDER BY t.bank_account

Thanks.

Please update your question to include a text representation of your expected output - not all of us are able to see images. — Boneist
– Boneist, Commented Jun 2, 2016 at 11:41

MT0 · Accepted Answer · 2016-06-02 20:46:20Z

4

Oracle Setup:

CREATE TABLE my_table ( bank_account, bank_id, amount ) AS
SELECT 123, 600, 1500 FROM DUAL UNION ALL
SELECT 123, 600, 2500 FROM DUAL UNION ALL
SELECT 123, 600, 3500 FROM DUAL UNION ALL
SELECT 123, 700,  500 FROM DUAL UNION ALL
SELECT 123, 700, 1000 FROM DUAL UNION ALL
SELECT 456, 800, 2000 FROM DUAL UNION ALL
SELECT 456, 900, 2000 FROM DUAL UNION ALL
SELECT 456, 950, 4000 FROM DUAL;

Query:

SELECT bank_account,
       bank_id,
       total_amount,
       number_of_transactions,
       SUM( number_of_transactions ) OVER ( PARTITION BY bank_account ) AS total_num_trans,
       SUM( total_amount           ) OVER ( PARTITION BY bank_account ) AS total_am_trans,
       number_of_transactions
         / SUM( number_of_transactions ) OVER ( PARTITION BY bank_account )
         * 100 AS percentage_trans,
       total_amount
         / SUM( total_amount           ) OVER ( PARTITION BY bank_account )
         * 100 AS percentage_trans
FROM   (
  SELECT   bank_account,
           bank_id,
           count(*)    AS number_of_transactions,
           sum(amount) AS total_amount
  FROM     my_table
  GROUP BY bank_account
         , bank_id
)

Output:

BANK_ACCOUNT    BANK_ID TOTAL_AMOUNT NUMBER_OF_TRANSACTIONS TOTAL_NUM_TRANS TOTAL_AM_TRANS PERCENTAGE_TRANS PERCENTAGE_TRANS
------------ ---------- ------------ ---------------------- --------------- -------------- ---------------- ----------------
         123        600         7500                      3               5           9000               60       83.3333333 
         123        700         1500                      2               5           9000               40       16.6666667 
         456        800         2000                      1               3           8000       33.3333333               25 
         456        900         2000                      1               3           8000       33.3333333               25 
         456        950         4000                      1               3           8000       33.3333333               50

edited Jun 2, 2016 at 20:46

answered Jun 2, 2016 at 11:45

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Arulkumar Over a year ago

Hope ORDER BY bank_account is not required in the sub-query

MT0 Over a year ago

@Arulkumar Thanks, updated. No, it is not needed but I had just copied the OP's query and didn't remove that bit when I tidied it all up.

Milan Stojković Over a year ago

Tried and it had some success. Apparently for the column total_num_trans it sums up by bank_id, and not by bank_account. For example: if we're looking at rows 4 and 8 total_num_trans should be 6. Here is the screenshot I've made on my data (I'm sorry I didn't ordered it by bank_account to make it more readable): onedrive.live.com/… and it should look like this: onedrive.live.com/…

MT0 Over a year ago

@MilanStojković I've updated with the output of the query based on your sample data - it outputs the expected values detailed in the OP. Without any other input data (formatted as DML statements) there is not much more I can debug as I have no way of replicating your output. If it is not summing by the account then check you have got the correct columns in the PARTITION BY clauses.

Milan Stojković Over a year ago

@MT0 you don't need DML statement, because you've done it :) With this statement I've got exactly what I need. Thank you so much!

Blank · Accepted Answer · 2016-06-02 11:54:48Z

0

Try this;)

select t1.*, t2.total_num_trans, t2.total_am_trans, (t1.number_of_transactions / t2.total_num_trans) * 100 as percentage_trans, (t1.total_amount / t2.total_am_trans) * 100 as percentage_amount
from (
    select bank_account, bank_id, sum(amount) as total_amount, count(1) as number_of_transactions
        from my_table
        group by bank_account, bank_id) t1
left join (
    select bank_account, sum(total_amount) as total_am_trans, sum(number_of_transactions) as total_num_trans
    from (
        select bank_account, bank_id, sum(amount) as total_amount, count(1) as number_of_transactions
        from my_table
        group by bank_account, bank_id ) t
group by bank_account ) t2 on t1.bank_account = t2.bank_account
order by t1.bank_account

answered Jun 2, 2016 at 11:54

Blank

12.4k1 gold badge16 silver badges34 bronze badges

1 Comment

Milan Stojković Over a year ago

Thanks Reno, it extracts the data that I actually need. The only problem is that I have to implement this statement in a system where the table that I'm getting data from is very, very big. Imagine all of the transactions made in a bank for the last two years. So if I use join with double select, I'm afraid that it would take a lot of time to finish. Thank you for your effort.

collapsar · Accepted Answer · 2016-06-02 12:55:42Z

Try to join 2 aggregations, the coarse one only groups by bank account, the finer one also by bank id.

      SELECT tfine.bank_account
           , tfine.bank_id
           , tfine.total_amount
           , tfine.number_of_transactions
           , tcoarse.total_num_trans
           , tcoarse.total_am_trans
        FROM (              
                  SELECT t1.bank_account
                       , t1.bank_id
                       , count(*)         number_of_transactions
                       , sum(t1.amount)   total_amount
                    FROM my_table t1 
                GROUP BY t1.bank_account
                       , t1.bank_id
             ) tfine
        JOIN (
                  SELECT t2.bank_account
                       , count(*)         total_num_trans
                       , sum(t2.amount)   total_am_trans
                    FROM my_table t2
                GROUP BY t2.bank_account
             ) tcoarse
          ON tcoarse.bank_account = tfine.bank_account
    ORDER BY tfine.bank_account
           , tfine.bank_id
           ;

Online demo on ideone.

Collectives™ on Stack Overflow

How to use SUM and COUNT and add the results as a resulting column

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related