Anyone help me with this aggregation problem? Thanks

Transact-SQL
1

I have two sample tables, customer_payment and customer_info. I hope to compete the following table for all domestic(USA) transactions, that is aggregation total number of Loans and total amount of payment in each state with each payment_type (Check, Wire or ACH)

Payment_Type | State | Num_Trans | Trans_Vol
CHECK | CA
CHECK | IL
CHECK | IA
WIRE | CA
WIRE | IL
WIRE | IA
ACH | CA
ACH | IL
ACH | IA

CREATE TABLE customer_payment
         (
                Customer_Nbr_Txt	VARCHAR(255),
				Loan_Nbr				BIGINT,
				Trans_Amt           decimal(22,2),
				Payment_tp         VARCHAR(255)
		 )
		 
 INSERT INTO  customer_payment

VALUES
('007849', '11613577', '1000','ACH'),
('007849', '11613578', '200', 'ACH'),
('007849', '11613579', '450', 'ACH'),
('007849', '11613580', '123', 'CHECK'),
('008120', '14364941', '321', 'ACH'),
('008120', '14364942', '330', 'WIRE'),
('008120', '14364946', '1587', 'WIRE'),
('008120', '14364942', '1000', 'WIRE'),
('009522', '12345678', '331', 'CHECK'),
('009522', '10234567', '32', 'WIRE')




 CREATE TABLE customer_info
         (
                Customer_Nbr_Txt	VARCHAR(255),
				Address_Country		VARCHAR(255),
				Address_State       VARCHAR(255)
				
		 )
		 
 INSERT INTO  customer_info


VALUES
('007849', 'USA', 'IL'),
('008120', 'USA', 'IA'),
('009522', 'USA', 'CA'),
('000001', 'USA', 'CA'),
('000019', 'USA', 'PA'),
('000025', 'CAN', 'BC')
2

do you have any sample output?

if I understand your requirements correctly, the query should be quite straightforward. To start off, you can try to:

COUNT the loan_nbr
SUM the Trans_Amt
GROUP the Payment_tp and Address_State.