SQL query for monthly bills with amounts paid

Question

The idea is to get a listing of monthly bills with amounts paid. The subquery does a total through a group by using bill_id and party_id.

I wish to know how it can be improved. I'm not comfortable with JOINs and am willing to learn.

SELECT
    bill.id as bill_id,
    master.id as party_id,
    master.name as party_name,
    `amountExcVat`,
    bill.vat,
    amountExcVat + bill.vat as 'amtIncVat',
    `purchasedDate`,
    (SELECT SUM( cashPaid ) + SUM( chequePaid ) 
     FROM  `partypayment`
     WHERE party_id = partyId AND bill_id = partyBillId
     GROUP BY partyId, partyBillId)
    as 'Amount Paid(cash+chq)',
    bill.status
FROM `partybill` as bill, `partymaster` as master, partypayment as payment
WHERE
    bill.partyId = master.id
    AND
    bill.partyId = payment.id
    AND
    UPPER(MONTHNAME(`purchasedDate`)) = 'OCTOBER'
    AND
    YEAR(`purchasedDate`) = '2013'

Tables are as follows. They provided by a third party so I can't modify them.

create table partymaster (
id INT(4)
,name varchar(100) PRIMARY KEY
,address varchar(200)
,contactNo1 BIGINT(14)
,contactNo2 BIGINT(14)
);

---

create table partypurchase (
id  INT(10)
,partyId INT(4) references partymaster(id)
,head varchar(100)
,brand varchar(100)
,size varchar(10)
,costPrice INT(6)
,sellingPrice INT(6)
,quantity INT(4)
,totalCost INT(10)
,purchasedDate date
);

---

create table partybill (
id INT(10) PRIMARY KEY
,partyId INT(4) references partymaster(id)
,amountExcVat INT(10)
,vat INT(10)
,amountIncVat INT(10)
,purchasedDate date
,expectedPaymentDate date
,status varchar(10)
);

---

create table partypayment (
id INT(10) PRIMARY KEY
,partyId INT(4) references partymaster(id)
,partyBillId INT(10) references partybill(id)
,cashPaid INT(10)
,chequePaid INT(10)
,chequeBankName varchar(50)
,chequeNumber INT(10)
,chequeDate date
,paymentDate date
);

Answer 1

Do you really need the subquery?

SELECT
bill.id as bill_id,
master.id as party_id,
master.name as party_name,
`amountExcVat`,
bill.vat,
amountExcVat + bill.vat as 'amtIncVat',
`purchasedDate`,
bill.status,
SUM(payment.cashPaid) + SUM(payment.chequePaid) as 'Amount Paid(cash+chq)',
FROM `partybill` as bill, `partymaster` as master, partypayment as payment
WHERE
party_id = partyId AND
bill_id = partyBillId AND
bill.partyId = master.id AND
bill.partyId = payment.id AND
UPPER(MONTHNAME(`purchasedDate`)) = 'OCTOBER' AND
YEAR(`purchasedDate`) = '2013'
GROUP BY partyId, partyBillId)

(I just wrote this without testing it so it might not work right out of the box)

Since you are already querying partypayment you should be able to do this