Query to sum and count values in different tables

Question

I have this query below to spool all accounts in the bank. Now i want to

sum the clr_bal_amt for each product type
Count the no of accountsNo for each of the products
count the no of accountNO fro each Branch_id

please help

 SELECT  accountNO,
         Product_type,
         gam.Branch_id,
         Branch_Name,
         CLR_BAL_AMT
 FROM    tbaadm.gam,
         tbaadm.gac,
         tbaadm.gsp,
         tbaadm.sol
 WHERE   gam.acid = gac.acid
         AND gam.sol_id = sol.sol_id
         AND gam.schm_code = gsp.schm_code
         AND acct_ownership <> 'O'
         AND acct_CLS_FLG = 'N'
         AND gam.del_flg = 'N'

Product_type is in gsp

Brancd_id and Branch_name are in sol

AccountNo and clr_bal_amt are in gam

Thank you

Answer 1

Going by the filtering reasons you supplied:

The Select to supply the first two requirements:

 SELECT  Product_type,
         SUM(CLR_BAL_AMT) AccountBalanceSum,
         COUNT(AccountNo) AccountNoCount
 FROM    tbaadm.gam
 JOIN    tbaadm.gac ON gam.acid = gac.acid
 JOIN    tbaadm.gsp ON gam.schm_code = gsp.schm_code
 JOIN    tbaadm.sol ON gam.sol_id = sol.sol_id
 WHERE   acct_ownership <> 'O'
         AND acct_CLS_FLG = 'N'
         AND gam.del_flg = 'N'
 GROUP BY Product_type

The select to supply the third requirement

 SELECT  Branch_Id
         COUNT(AccountNo) AccountNoCount
 FROM    tbaadm.gam
 JOIN    tbaadm.gac ON gam.acid = gac.acid
 JOIN    tbaadm.gsp ON gam.schm_code = gsp.schm_code
 JOIN    tbaadm.sol ON gam.sol_id = sol.sol_id
 WHERE   acct_ownership <> 'O'
         AND acct_CLS_FLG = 'N'
         AND gam.del_flg = 'N'
 GROUP BY Branch_Id

These are split into two queries because you are aggregating on two different criteria.

	Follow @Ask_SSC
	Follow Ask SSC on Facebook
	Find Ask SSC on Google+
	Find us on LinkedIn

Query to sum and count values in different tables

Follow this question

Related Questions