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!
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.
Comments