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.

Query to sum and count values in different tables

Follow this question

Related Questions