CODEWITHSUNDEEP
mysqlsql
DomainFlag
DomainFlag

Reputation: 564

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?

Upvotes: 0

Views: 47

Answers (1)

Barmar
Barmar

Reputation: 780869

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

Upvotes: 1

Related Questions