fork download
copy 
  1. CREATE TABLE my_table (
  2.       bank_account  NUMBER
  3.     , bank_id       NUMBER
  4.     , amount        NUMBER
  5. );
  6.  
  7. INSERT INTO my_table (bank_account,bank_id, amount ) VALUES( 123, 600, 1500 );
  8. INSERT INTO my_table (bank_account,bank_id, amount ) VALUES( 123, 600, 2500 );
  9. INSERT INTO my_table (bank_account,bank_id, amount ) VALUES( 123, 600, 3500 );
  10. INSERT INTO my_table (bank_account,bank_id, amount ) VALUES( 123, 700,  500 );
  11. INSERT INTO my_table (bank_account,bank_id, amount ) VALUES( 123, 700, 1000 );
  12. INSERT INTO my_table (bank_account,bank_id, amount ) VALUES( 456, 800, 2000 );
  13. INSERT INTO my_table (bank_account,bank_id, amount ) VALUES( 456, 900, 2000 );
  14. INSERT INTO my_table (bank_account,bank_id, amount ) VALUES( 456, 950, 4000 );
  15.  
  16.  
  17.  
  18.           SELECT tfine.bank_account
  19.                , tfine.bank_id
  20.                , tfine.total_amount
  21.                , tfine.number_of_transactions
  22.                , tcoarse.total_num_trans
  23.                , tcoarse.total_am_trans
  24.             FROM (              
  25.                       SELECT t1.bank_account
  26.                            , t1.bank_id
  27.                            , count(*)         number_of_transactions
  28.                            , sum(t1.amount)   total_amount
  29.                         FROM my_table t1 
  30.                     GROUP BY t1.bank_account
  31.                            , t1.bank_id
  32.                  ) tfine
  33.             JOIN (
  34.                       SELECT t2.bank_account
  35.                            , count(*)         total_num_trans
  36.                            , sum(t2.amount)   total_am_trans
  37.                         FROM my_table t2
  38.                     GROUP BY t2.bank_account
  39.                  ) tcoarse
  40.               ON tcoarse.bank_account = tfine.bank_account
  41.         ORDER BY tfine.bank_account
  42.                , tfine.bank_id
  43.                ;
  44.  
Success #stdin #stdout 0s 3440KB
comments (?)
stdin
copy 
Standard input is empty
stdout
copy 
123|600|7500|3|5|9000
123|700|1500|2|5|9000
456|800|2000|1|3|8000
456|900|2000|1|3|8000
456|950|4000|1|3|8000
https://ideone.com/vV2OUQ
language:
SQLite (sqlite 3.27.2)
created:
9 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!