Search the Community
Showing results for tags 'union all'.
-
I'm stuck like a wheel barrow in the mud. Wondering if someone might be able to help me write this query. I have a table where status change dates are held. I'm trying to build a query that sums each of the columns by date. In the end.... I should have a column of dates in the first column. several columns as listed below. (created, offers, contracts, closed, sold) Then the total number of records with dates that match the row date. (It think that makes sense) So I can tell ... how many records were created on each day. how many offers were made on each day etc etc. Here is the query I have so far which doesn't quite work. Display is attached. select * from ( select `date`, sum(created) created, sum(offers) offers, sum(contracts) contracts, sum(closed) closed, sum(sold) sold from ( select DATE(from_unixtime(created)) `date`, count(*) created, 0 as offers, 0 as contracts, 0 as closed, 0 as sold from properties union all select DATE(from_unixtime(offer_date)) `date`, 0 as created, count(*) as offers, 0 as contracts, 0 as closed, 0 as sold from properties union all select DATE(from_unixtime(contract_date)) `date`, 0 as created, 0 as offers, count(*) as contracts, 0 as closed, 0 as sold from properties union all select DATE(from_unixtime(purchase_date)) `date`, 0 as created, 0 as offers, 0 as contracts, count(*) as closed, 0 as sold from properties union all select DATE(from_unixtime(sale_date)) `date`, 0 as created, 0 as offers, 0 as contracts, 0 as closed, count(*) as sold from properties )a group by a.`date` )b