GROUP BY and SUM distinct date across 2 tables
- by kenitech
I'm not sure if this is possible in one mysql query so I might just combine the results via php.
I have 2 tables: 'users' and 'billing'
I'm trying to group summed activity for every date that is available in these two tables. 'users' is not historical data but 'billing' contains a record for each transaction.
In this example I am showing a user's status which I'd like to sum for created date and deposit amounts that I would also like to sum by created date. I realize there is a bit of a disconnect between the data but I'd like to some all of it together and display it as seen below. This will show me an overview of all of the users by when they were created and what the current statuses are next to total transactions.
I've tried UNION as well as LEFT JOIN but I can't seem to get either to work.
Union example is pretty close but doesn't combine the dates into one row.
(
SELECT
created,
SUM(status) as totalActive,
NULL as totalDeposit
FROM users
GROUP BY created
)
UNION
(
SELECT
created,
NULL as totalActive,
SUM(transactionAmount) as totalDeposit
FROM billing
GROUP BY created
)
I've also tried using a date lookup table and joining on the dates but the SUM values are being added multiple times.
note: I don't care about the userIds at all but have it in here for the example.
users table
(where status of '1' denotes "active")
(one record for each user)
created | userId | status
2010-03-01 | 10 | 0
2010-03-01 | 11 | 1
2010-03-01 | 12 | 1
2010-03-10 | 13 | 0
2010-03-12 | 14 | 1
2010-03-12 | 15 | 1
2010-03-13 | 16 | 0
2010-03-15 | 17 | 1
billing table
(record created for every instance of a billing "transaction"
created | userId | transactionAmount
2010-03-01 | 10 | 50
2010-03-01 | 18 | 50
2010-03-01 | 19 | 100
2010-03-10 | 89 | 55
2010-03-15 | 16 | 50
2010-03-15 | 12 | 90
2010-03-22 | 99 | 150
desired result:
created | sumStatusActive | sumStatusInactive | sumTransactions
2010-03-01 | 2 | 1 | 200
2010-03-10 | 0 | 1 | 55
2010-03-12 | 2 | 0 | 0
2010-03-13 | 0 | 0 | 0
2010-03-15 | 1 | 0 | 140
2010-03-22 | 0 | 0 | 150
Table dump:
CREATE TABLE IF NOT EXISTS `users` (
`created` date NOT NULL,
`userId` int(11) NOT NULL,
`status` smallint(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `users` (`created`, `userId`, `status`) VALUES
('2010-03-01', 10, 0),
('2010-03-01', 11, 1),
('2010-03-01', 12, 1),
('2010-03-10', 13, 0),
('2010-03-12', 14, 1),
('2010-03-12', 15, 1),
('2010-03-13', 16, 0),
('2010-03-15', 17, 1);
CREATE TABLE IF NOT EXISTS `billing` (
`created` date NOT NULL,
`userId` int(11) NOT NULL,
`transactionAmount` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `billing` (`created`, `userId`, `transactionAmount`) VALUES
('2010-03-01', 10, 50),
('2010-03-01', 18, 50),
('2010-03-01', 19, 100),
('2010-03-10', 89, 55),
('2010-03-15', 16, 50),
('2010-03-15', 12, 90),
('2010-03-22', 99, 150);
