Mysql join query

Question

I'm using two tables in the database. These tables look like this:

Table A:

    id    |    date
 ----------------------
   12001  | 2011-01-01
   13567  | 2011-01-04
   13567  | 2011-01-04 
   11546  | 2011-01-07
   13567  | 2011-01-07
   18000  | 2011-01-08

Table B:

   user   |    date      | amount
 ----------------------------------
   15467  |  2011-01-04  |  140
   14568  |  2011-01-04  |  120
   14563  |  2011-01-05  |  140
   12341  |  2011-01-07  |  140 
   18000  |  2011-01-08  |  120

I need a query that will join these the two tables.

The first query should result in a total number of users from table A group by date and the number of unique users from table A grouped by date. That query looks like:

SELECT COUNT(DISTINCT id) AS uniq, COUNT(*) AS total,  format_date(date, '%Y-%m-%d') as date FROM A  GROUP BY date 

From the second table I need the sum of the amounts grouped by dates. That query looks like:

SELECT SUM(amount) AS total_amount FROM B GROUP BY DATE_FORMAT( date,  '%Y-%m-%d' )

What I want to do is to merge these two queries into one on column "date", and that as a result I get the following list:

   date     |   unique  |   total  |    amount
 -----------------------------------------------
 2011-01-01 |     1     |     1    |     0
 2011-01-04 |     1     |     2    |    260
 2011-01-05 |     0     |     0    |    140
 2011-01-07 |     2     |     2    |    140
 2011-01-08 |     1     |     1    |    120

How can I do that using one query? Thanks for all suggestions.

Answer 1

select date_format(a.date, '%Y-%m-%d') as date, a.uniq, a.total, ifnull(b.amount, 0) as amount 
from (  
    select count(distinct id) as uniq, count(*) as total, date
    from tablea
    group by date
) a
left join (
    select sum(amount) as amount, date
    from tableb
    group by date
) b on a.date = b.date
order by a.date

I assume that field date is a datetime type. It's better to format output fields in final result set (date field in this case).

Your queries are fine everything they need is a join.