CODEWITHSUNDEEP
mysqlsqljoinouter-joinfull-outer-join
dandan
dandan

Reputation: 1136

MySQL - Full outer join on same table using COUNT

I am trying to generate a table in the following format. 

Proday | 2014-04-01 | 2014-03-01
--------------------------------
  1    |      12    |     17
  2    |       6    |      0
  7    |       0    |     24
 13    |       3    |      7

Prodays (duration between two timestamps) is a calculated value and the data for months is a COUNT. I can output the data for a single month, but am having troubles joining queries to additional months. The index (prodays) may not match for each month. e.g.. 2014-04-01 may not have any data for Prodays 7, whereas 2014-03-01 may not have Proday 2. Should indicate with 0 or null.

I suspect FULL OUTER JOIN is what should do the trick. But have read that's not possible in Mysql?

This is the query to get data for a single month:

SELECT round((protime - createtime) / 86400) AS prodays, COUNT(id) AS '2014-04-01'
FROM  `tbl_users` as t1
WHERE status = 1 AND DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y-%m-%d') >= '2014-04-01'
AND DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y-%m-%d') <= LAST_DAY('2014-04-01')
GROUP BY prodays
ORDER BY `prodays` ASC

How can I join/union an additional query to create a column for 2014-03-01?

Upvotes: 0

Views: 641

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You want to use conditional aggregation -- that is, move the filtering logic from the where clause to the select clause:

SELECT round((protime - createtime) / 86400) AS prodays,
       sum(DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y-%m-%d') >= '2014-04-01' AND 
           DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y-%m-%d') <= LAST_DAY('2014-04-01')
          ) as `2014-04-01`,
       sum(DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y-%m-%d') >= '2014-03-01' AND 
           DATE_FORMAT(FROM_UNIXTIME(createtime),'%Y-%m-%d') <= LAST_DAY('2014-03-01')
          ) as `2014-03-01`
FROM  `tbl_users` as t1
WHERE status = 1
GROUP BY prodays
ORDER BY `prodays` ASC;

Upvotes: 1

Related Questions