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 JOIN
s 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 );