x

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

more ▼

asked Nov 21, 2012 at 08:53 AM in Default

avatar image

koded01
10 1 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 21, 2012 at 10:00 AM

avatar image

WilliamD
26.2k 18 33 48

,this is all i needed. thanks a million.

Nov 21, 2012 at 11:26 AM koded01
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066

asked: Nov 21, 2012 at 08:53 AM

Seen: 1158 times

Last Updated: Nov 21, 2012 at 11:56 AM

Copyright 2016 Redgate Software. Privacy Policy