DomainFlag DomainFlag - 2 months ago 7
MySQL Question

Error when selecting db data with SQL query?

I have these 3 tables:

adhocbills
table:

CREATE TABLE IF NOT EXISTS `adhocbills`
(
`sequence` bigint(11) NOT NULL AUTO_INCREMENT,
`status` varchar(200) NOT NULL,
`type` varchar(200) NOT NULL,
`invoice_number` varchar(200) NOT NULL,
PRIMARY KEY (`sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `adhocbills` (`sequence`, `status`, `type`, `invoice_number`)
VALUES (1, 'Completed', 'Invoice', '1234');


adhocbills_lineitems
table:

CREATE TABLE IF NOT EXISTS `adhocbills_lineitems`
(
`sequence` bigint(11) NOT NULL AUTO_INCREMENT,
`bill_seq` varchar(20) NOT NULL,
`service` varchar(200) NOT NULL,
PRIMARY KEY (`sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `adhocbills_lineitems` (`sequence`, `bill_seq`, `service`)
VALUES (1, '1', 'Service 1'), (2, '1', 'Service 2'),
(3, '1', 'Service 2');


billing_invoices
table:

CREATE TABLE IF NOT EXISTS `billing_invoices`
(
`sequence` bigint(20) NOT NULL AUTO_INCREMENT,
`invoice_number` varchar(200) NOT NULL,
`sub_total` float NOT NULL,
`vat_amount` float NOT NULL,
`grand_total` float NOT NULL,
`datetime` date NOT NULL,
`invoice_type` varchar(100) NOT NULL,
`status` varchar(200) NOT NULL DEFAULT 'Unpaid',
`total_charges` float NOT NULL,
`proforma` varchar(1) NOT NULL,
PRIMARY KEY (`sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `billing_invoices`
(`sequence`, `invoice_number`, `sub_total`, `vat_amount`, `grand_total`, `datetime`, `invoice_type`, `status`, `total_charges`, `proforma`)
VALUES
(1, '1234', 100, 20, 120, '2016-09-01', 'Invoice', 'Unpaid', 100, '');


And I have this select query:

SELECT
COUNT(i.sequence) as counter,
l.service as service,
SUM(i.sub_total) as sub_total,
SUM(i.total_charges) as total_charges,
SUM(i.vat_amount) as vat_amount,
SUM(i.grand_total) as grand_total
FROM
billing_invoices i
LEFT JOIN
adhocbills a ON a.invoice_number = i.invoice_number
LEFT JOIN
adhocbills_lineitems l ON a.sequence = l.bill_seq
WHERE
i.proforma <> 'Y'
AND i.invoice_type = 'Invoice'
AND a.status = 'Completed'
AND DATE(i.datetime) >= '2016-09-01'
AND DATE(i.datetime) <= '2016-09-30'
GROUP BY
l.service
ORDER BY
grand_total DESC


What I want to do:

I want to select all rows from
billing_invoices
for the current month and link this table to the following:

adhocbills.invoice_number = billing_invoices.invoice_number
adhocbills.sequence = adhocbills_lineitems.bill_seq


and using the existing WHERE clauses.

Then I group by
adhocbills_lineitems.service


The sample data shows this:

enter image description here

which totals
1035.75
however the correct amount is
1017.26
so it is adding on an extra
18.50


The extra 18.50 is coming from rows in adhocbills_lineitems:

bill_seq = 1068
unitprice = 8.50(new column, not important)
quantity = 1(new column, not important)

bill_seq = 1068
unitprice = 10(new column, not important)
quantity = 1(new column, not important)


so these two rows both link to
adhocbills.sequence = '1068'


it seems that when there is more than one row in
adhocbills_lineitems
with a different
adhocbills_lineitems.service
it is duplicating it

Mini working example:

Is that I get this result:

counter service sub_total total_charges vat_amount grand_total
2 Service 2 200 200 40 240
1 Service 1 100 100 20 120


Which at row with service 2 is doubling dunno why?

Answer

Instead of joining with the table, join with a subquery that just returns the unique columns that you care about, and not all the duplicates.

SELECT 
     COUNT(i.sequence) as counter, 
     l.service as service, 
     SUM(i.sub_total) as sub_total, 
     SUM(i.total_charges) as total_charges, 
     SUM(i.vat_amount) as vat_amount, 
     SUM(i.grand_total) as grand_total 
     FROM billing_invoices i 
LEFT JOIN adhocbills a ON a.invoice_number = i.invoice_number 
LEFT JOIN (
    SELECT DISTINCT service, bill_seq
    FROM adhocbills_lineitems
) l ON a.sequence = l.bill_seq 
WHERE i.proforma <> 'Y' AND i.invoice_type = 'Invoice' AND a.status = 'Completed' 
    AND DATE(i.datetime) >= '2016-09-01' AND DATE(i.datetime) <= '2016-09-30' 
GROUP BY l.service 
ORDER BY grand_total DESC