GROUP BY and SUM distinct date across 2 tables

Question

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);

Answer 1

Try this:

Select created, sum(status) as totalActive, sum(transactionAmount) as totalDeposit
From
( (
  SELECT
  created,
  status,
  0 as transactionAmount
  FROM users
)
UNION
(
  SELECT
  created,
  0 as status,
  transactionAmount
  FROM billing
) ) as x group by created

Answer 2

Ah. Thanks to p.g.I.hall I was able to modify the query and get my desired result:

Select 
createdDate,
SUM(statusSum),
SUM(transactionAmountSum)

From
( (
  SELECT
  created as createdDate,
  sum(status) as statusSum,
 '0' as transactionAmountSum
  FROM users
  GROUP BY createdDate
)
UNION
(
  SELECT
  created as createdDate,
  '0' as statusSum,
  sum(transactionAmount) as transactionAmountSum
  FROM billing
  GROUP BY createdDate
) ) 

as x
group by createdDate

Answer 3

A word of warning - your users table does not have a unique key. I'm going to take a wild guess here and say that you should probably create a primary key with the userId column.

A table without primary keys means you have no protection against bad, duplicate data slipping into your tables! Aaaaaah!