MySQL Query - How do I get a SUM with GROUP BY and WHERE condition and use LEFT OUTER JOIN?

compcentral

I'm not sure how to get the result that I expect and I've tried everything. I also need to be able to get the expected results using one query.

Here are the two tables with some sample data:

--
-- Table structure for table `accounts`
--

CREATE TABLE IF NOT EXISTS `accounts` (
`id` int(11) NOT NULL,
`name` varchar(200) NOT NULL,
`type_id` int(11) NOT NULL,
`description` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Data for table `accounts`
--

INSERT INTO `accounts` (`id`, `name`, `type_id`, `description`) VALUES
(100, 'DUES', 0, NULL),
(101, 'NET WEEKLY PAYROLL', 0, NULL),
(111, 'FEDERAL TAX DEPOSITS', 0, 'tax stuff'),
(113, 'UNITED ASSOCIATION PAYMENTS', 0, NULL),
(114, 'OFFICERS MEETING ALLOWANCES', 0, NULL);

--
-- Table structure for table `checks`
--

CREATE TABLE IF NOT EXISTS `checks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batch_id` int(11) DEFAULT NULL,
  `entry_date` date NOT NULL,
  `account_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `posted` tinyint(4) NOT NULL DEFAULT '0',
  `vendor_id` int(11) DEFAULT NULL,
  `check_num` int(11) NOT NULL,
  `voided` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `checks`
--

INSERT INTO `checks` (`id`, `batch_id`, `entry_date`, `account_id`, `amount`, `description`, `posted`, `vendor_id`, `check_num`, `voided`) VALUES
(1, NULL, '2013-01-21', 111, '77.44', 'Last Year', 0, 1, 100, 0),
(2, NULL, '2014-01-21', 111, '521.11', 'Test Stuff', 0, 1, 101, 0),
(3, NULL, '2014-01-20', 101, '121.11', 'More Tests', 0, 1, 222, 0),
(4, NULL, '2014-01-02', 101, '150.00', 'test', 0, 4, 213, 0);

I want to create a list of all accounts with a month-to-date sum as an added field. Here is the query to get the month-to-date sum without joining the accounts table:

SELECT *, SUM(amount) as mtd FROM `checks` WHERE `entry_date` > '2014-01-01' GROUP BY `account_id`

... and here is what i used to get all the accounts joined to checks table:

SELECT * FROM `accounts` LEFT OUTER JOIN `checks` ON `checks.account_id` = `accounts.id`

I just can't seem to combine these two correctly to get the expected results. Please help!

Gordon Linoff

I think this solves your problem:

SELECT a.*, SUM(c.amount) as mtd
FROM accounts a left outer join
     checks c
     ON (a.id = c.account_id) and c.entry_date >= '2014-01-01'
GROUP BY a.account_id;

This will return all accounts, even those with no activity in January. I changed the date condition to >=, because that "feels" better as a month-to-date cutoff.

Collected from the Internet

Please contact [email protected] to delete if infringement.