Query to sum and count values in different tables
I have this query below to spool all accounts in the bank. Now i want to 1. sum the clr_bal_amt for each product type 2. Count the no of accountsNo for each of the products 3. 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
Best Answer
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.
