need help selecting sum from another table

[Angelo Kaufmann]

for my project study.

i have 3 tables named sales, sales_payments,and sales_agent.

DROP TABLE IF EXISTS `sms2`.`sales_agent`;
CREATE TABLE `sms2`.`sales_agent` (
`Agent_Code` varchar(20) NOT NULL,
`First_Name` varchar(33) NOT NULL,
`Middle_Name` varchar(33) NOT NULL,
`Last_Name` varchar(33) NOT NULL,
`Address` varchar(100) NOT NULL,
`Mobile_No_1` varchar(20) NOT NULL,
`Mobile_No_2` varchar(20) NOT NULL,
`Marital_Status` varchar(20) NOT NULL,
`Birth_Date` datetime NOT NULL,
`Registration_Date` datetime NOT NULL,
`photo` longblob NOT NULL,
PRIMARY KEY (`Agent_Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS `sms2`.`sales`;
CREATE TABLE `sms2`.`sales` (
`Transaction_Code` varchar(20) NOT NULL,
`Agent_Code` varchar(20) NOT NULL,
`Transaction_Type` varchar(20) NOT NULL,
`Customer_Name` varchar(100) NOT NULL,
`Address` varchar(50) NOT NULL,
`Contact_Number` varchar(20) NOT NULL,
`Transaction_Date` datetime NOT NULL,
`IsEditable` varchar(5) DEFAULT NULL,
`number` int(10) unsigned NOT NULL,
PRIMARY KEY (`Transaction_Code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS `sms2`.`sales_payments`;
CREATE TABLE `sms2`.`sales_payments` (
`Transaction_Code` varchar(20) NOT NULL,
`Balance` float(15,2) NOT NULL,
`Total_Payable` float(15,2) NOT NULL,
`Grand_Total` float(15,2) NOT NULL,
`Amount_Paid` float(15,2) NOT NULL,
`New_Balance` float(15,2) NOT NULL,
`Overdue` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


sales and sales_payments are related through the Transaction_Code column

sales and sales_agents through column Agent_Code.

my problem is i want to select the SUM(sales_payments.Amount_Paid), sales_agent.Code for each Agent_Code between a dates regarding sales.Transaction_Date


in other words i want to get the total sales_payments.Amount_Paid for each sales_agent.Agent_Code ...

please need help thanks...