MySQL query with SELECT SUM() incorrectly doubles column value because of LEFT JOIN

Question

I am trying to get the total amount per month from 2 tables Here is my query

SELECT
    date_format(items.date, '%M %Y') as month, 
    COALESCE(SUM(items.amount), 0) as Rreceived, 
    COALESCE(SUM(issued_items.amount), 0) as Issued, 
    items.currency 
FROM
    items 
LEFT JOIN
    issued_items 
        ON date_format(items.date, '%Y-%m')=date_format(issued_items.date, '%Y-%m') 
        AND items.currency=issued_items.currency 
GROUP BY
    date_format(items.date, '%Y-%m')

Rreceived amount should be 100, but I am getting 200. Can you please help me correct the query?

SQL FIDDLE

CREATE TABLE IF NOT EXISTS issued_items (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `date` date NOT NULL,
    `purpose` varchar(255) NOT NULL,
    `amount` decimal(25,2) NOT NULL,
    `currency` varchar(3) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=7;

INSERT INTO issued_items (`id`,`date`,`purpose`,`amount`,`currency`) VALUES
    (5, '2013-05-13', 'Test', 100.00, 'USD'),
    (6, '2013-05-13', 'Test', 100.00, 'USD');

CREATE TABLE IF NOT EXISTS items (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `date` date NOT NULL,
    `purpose` varchar(255) NOT NULL,
    `currency` varchar(3) NOT NULL,
    `amount` decimal(25,2) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2;

INSERT INTO items (`id`,`date`,`purpose`,`currency`,`amount`) VALUES 
    (1, '2013-05-13', 'Test', 'USD', 100.00);

Requirement logic extension:

I also need to represent records from issued_items even if there is no record for the month-period in the items table.

Add this row to issued_items:

(7, '2013-07-01', 'Test', 100.00, 'USD')

The problem is you are joining on date so regardless of the join type you will get that result. I think you should look into redesign your schema, probably add item_id to issued_items. — vee
– vee, Commented Aug 2, 2013 at 5:55
@bew I am sorry, There is fiddle link I need the amount totals from items and issued_items table. Please have a look at fiddle. Thank you — Saleem
– Saleem, Commented Aug 2, 2013 at 6:04

peterm · Accepted Answer · 2013-08-03 08:40:05Z

2

Try this:

SELECT date_format(items.date, '%M %Y') as month, 
COALESCE(SUM(items.amount), 0) as Rreceived, 

  (SELECT SUM(amount)
  FROM issued_items
  WHERE date_format(items.date, '%Y-%m') = date_format(date, '%Y-%m') 
    AND items.currency = currency 
  ) AS Issued

FROM items
GROUP BY date_format(items.date, '%Y-%m')

edited Aug 3, 2013 at 8:40

peterm

93k16 gold badges157 silver badges162 bronze badges

answered Aug 2, 2013 at 5:58

Stephen O'Flynn

2,32923 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Stephen O'Flynn Over a year ago

Since your JOIN results in multiple rows, it will keep accumulating the Rreceived total, which you don't want. A sub-query is a better way to go.

Saleem Over a year ago

Thank you I am not sure about the sub-query and you have saved me. Thank you again

Ivan Buttinoni · Accepted Answer · 2013-08-03 08:19:15Z

Add a new answer related to the new http://sqlfiddle.com/#!2/9856e9/1 situation:

To avoid row loss I made a union on both table picking ALL possible month/currency combination, so I can do a left join on both table. Be aware, if a combination is not present in the union (Apr 2013/USD) no row is returned!

SELECT union_items.month, 
ifnull(items.amount,0) as Received, 
ifnull(issued_items.amount,0) as Issued, 
union_items.currency 
FROM ( 
      select 
      date_format(items.date, '%M %Y') as month, items.currency 
      FROM items  
      GROUP BY date_format(items.date, '%Y-%m')
      UNION 
      select 
      date_format(issued_items.date, '%M %Y') as month, issued_items.currency 
      FROM issued_items  
      GROUP BY date_format(issued_items.date, '%Y-%m')
) union_items
LEFT JOIN
(
SELECT date_format(items.date, '%M %Y') as month, 
COALESCE(SUM(items.amount), 0) as amount, 
items.currency 
FROM items  
GROUP BY date_format(items.date, '%Y-%m')
) items 
ON items.month=union_items.month 
AND items.currency=union_items.currency 
LEFT JOIN (
SELECT date_format(issued_items.date, '%M %Y') as month, 

  COALESCE(SUM(issued_items.amount), 0) as amount, 
issued_items.currency 
FROM issued_items
  GROUP BY date_format(issued_items.date, '%Y-%m')

)
issued_items 
ON union_items.month=issued_items.month 
AND union_items.currency=issued_items.currency

Ivan Buttinoni · Accepted Answer · 2013-08-02 13:31:16Z

1

@Stephen: your solution issue a subquery on issue_items for each row on items, isn't?

I think this can be a cheaper solution:

SELECT items.month, 
items.amount as Rreceived, 
issued_items.amount as Issued, 
items.currency 
FROM ( 

SELECT date_format(items.date, '%M %Y') as month, 
COALESCE(SUM(items.amount), 0) as amount, 
items.currency 
FROM items  
GROUP BY date_format(items.date, '%Y-%m')
) items 
LEFT JOIN (
SELECT date_format(issued_items.date, '%M %Y') as month, 

  COALESCE(SUM(issued_items.amount), 0) as amount, 
issued_items.currency 
FROM issued_items
  GROUP BY date_format(issued_items.date, '%Y-%m')

)
issued_items 
ON items.month=issued_items.month 
AND items.currency=issued_items.currency

CHANGE IFNULL can solve your problem (as NVL in Oracle)

SELECT items.month, 
ifnull(items.amount,0) as Rreceived, 
ifnull(issued_items.amount,0) as Issued, 
items.currency 
FROM ( 

SELECT date_format(items.date, '%M %Y') as month, 
COALESCE(SUM(items.amount), 0) as amount, 
items.currency 
FROM items  
GROUP BY date_format(items.date, '%Y-%m')
) items 
LEFT JOIN (
SELECT date_format(issued_items.date, '%M %Y') as month, 

  COALESCE(SUM(issued_items.amount), 0) as amount, 
issued_items.currency 
FROM issued_items
  GROUP BY date_format(issued_items.date, '%Y-%m')

)
issued_items 
ON items.month=issued_items.month 
AND items.currency=issued_items.currency

edited Aug 2, 2013 at 13:31

answered Aug 2, 2013 at 6:33

Ivan Buttinoni

4,1451 gold badge27 silver badges46 bronze badges

3 Comments

Saleem Over a year ago

Thank you Ivan Buttinoni, I need you help again sqlfiddle.com/#!2/9856e9/1 is not displying the issued_item for July as this is not in itsms table. Can you help me here Thanks

Ivan Buttinoni Over a year ago

see my "change" section

Saleem Over a year ago

Thank you foe help, but its not seem to work as expected. I need to display the issued record for july too. But this query only getting the records that are in items table. July record is not in items table but only in issued_items table. I am sorry to bother you but if you could help. Thanks again!

Collectives™ on Stack Overflow

MySQL query with SELECT SUM() incorrectly doubles column value because of LEFT JOIN

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related