Hello SQL Gurus
I am trying to write an sql statement that will return a row which contains
vendor info, how much he owes, total payments and balance due.
The results should look like this :
ABC,VENDOR ONE,333.33,199.98,133.35
DEF,VENDOR TWO,333.33, 0.00,333.33
The statement uses 3 tables and contains following data
//////////////// VENDORS \\\\\\\\\\\
CREATE TABLE vendor (
vendorno varchar(10) NOT NULL,
vendorname varchar(30) NOT NULL
);
INSERT INTO vendor VALUES( 'ABC', 'VENDOR ABC');
INSERT INTO vendor VALUES( 'DEF', 'VENDOR DEF');
//////////////// INVOICES \\\\\\\\\\\
CREATE TABLE invoices (
vendorno varchar(10) NOT NULL,
invdate date DEFAULT '0000-00-00' NOT NULL,
invoiceno varchar(10) NOT NULL,
total decimal(10,2) DEFAULT '0.00' NOT NULL
);
INSERT INTO invoices VALUES( 'ABC', '2002-02-01', '101', '111.11');
INSERT INTO invoices VALUES( 'ABC', '2002-02-02', '102', '222.22');
INSERT INTO invoices VALUES( 'DEF', '2002-02-02', '103', '333.33');
//////////////// PAYMENTS \\\\\\\\\\\
CREATE TABLE payments (
vendorno varchar(10) NOT NULL,
paydate date DEFAULT '0000-00-00' NOT NULL,
invoiceno varchar(10) NOT NULL,
paid decimal(10,2) DEFAULT '0.00' NOT NULL
);
INSERT INTO payments VALUES( 'ABC', '2002-03-01', '101', '55.55');
INSERT INTO payments VALUES( 'ABC', '2002-03-03', '102', '66.66');
INSERT INTO payments VALUES( 'ABC', '2002-03-04', '102', '77.77');
/////// DETAILED SQL STATEMENT \\\\\\\\\
I have tried this statement which returns a detailed listing but what I want is a summarized listing
select vendor.vendorno,vendor.vendorname,invoices.total, (payments.paid) ,
(invoices.total-payments.paid) as balance from vendor
LEFT JOIN invoices on (vendor.vendorno=invoices.vendorno)
LEFT JOIN payments on (vendor.vendorno=payments.vendorno) and (invoices.invoiceno=payments.invoiceno)
order by vendor.vendorno
////// SUMMARIZED STATEMENT \\\
This summarized listing returns duplicate values for total and balance.
select vendor.vendorno,vendor.vendorname,sum(invoices.total), sum(payments.paid) ,
sum(invoices.total-payments.paid) as balance from vendor
LEFT JOIN invoices on (vendor.vendorno=invoices.vendorno)
LEFT JOIN payments on (vendor.vendorno=payments.vendorno) and (invoices.invoiceno=payments.invoiceno)
group by vendor.vendorno
Am using Mysql 3.23.47 NT
