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.

Michał Powaga · Accepted Answer · 2011-12-03 17:27:36Z

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.

answered Dec 3, 2011 at 17:27

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql join query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related