CODEWITHSUNDEEP
mysqlsqljoinaggregate-functions
reefine
reefine

Reputation: 853

Join ON Date Group

I have 3 tables;

Table 1

id   date
1    1132123123
2    1232342341

etc

Table 2

id   date
1    1132123123
2    1232342341

etc

Table 3

id   date
1    1132123123
2    1232342341

etc

All "date" columns are unix timestamps.

I am trying to join these 3 tables and count totals for each table respectively grouped by:

FROM_UNIXTIME(date, '%m-%d-%Y')

Ideally, I'd like this result:

formatteddate   t1count   t2count   t3count
04-12-2011      2         2         2
04-13-2011      1         2         3

NOTE: The result doesn't match up to the example data, but I think it's pretty straight-forward.

Here's what I've tried so far:

SELECT
  FROM_UNIXTIME(t1.date, '%m-%d-%Y') as t1date,
  FROM_UNIXTIME(t2.date, '%m-%d-%Y') as t2date,
  FROM_UNIXTIME(t3.date, '%m-%d-%Y') as t3date, 
  count(t1.id) as t1count,
  count(t2.id) as t2count,
  count(t3.id) as t3count
FROM
  t1,t2,t3
GROUP BY
  t1date

The query doesn't even load. t3 contains lots of data (1 million + records). t1 & t2, not so much.

Upvotes: 2

Views: 238

Answers (1)

Nicola Cossu
Nicola Cossu

Reputation: 56357

select from_unixtime(date,'%m-%d-%Y') as d,
sum(tb=1) as tb1,
sum(tb=2) as tb2,
sum(tb=3) as tb3
from (
select date,1 as tb from t1
union all
select date,2 from t2
union all
select date,3 from t3) as t
group by d

Upvotes: 3

Related Questions