Complex query help

[sptrsn]

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

[Barand]

It would help if we knew what the data looks like. Table structure?

[sptrsn]

Sorry. Got distracted and forgot to include table info.

 

Here is the create with a little test data.

 

And here is a simplified version of the query. (fewer fields. If I can make this work... I can add the additional columns)

select * from (
	select  `date`,  sum(created) created, sum(offers) offers from
	(
		select DATE(from_unixtime(created))    `date`,        0 as created,        0 as offers from properties union all
		select DATE(from_unixtime(created))    `date`,    count(*) created,        0 as offers from properties union all
		select DATE(from_unixtime(offer_date)) `date`,        0 as created, count(*) as offers from properties 
	)a
	
	group by `date`
)b

CREATE TABLE IF NOT EXISTS `properties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` int(11) NOT NULL DEFAULT '0',
  `offer_date` int(11) NOT NULL DEFAULT '0',
  `contract_date` int(11) NOT NULL DEFAULT '0',
  `purchase_date` int(11) NOT NULL DEFAULT '0',
  `sale_date` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

INSERT INTO `properties` (`id`, `created`, `offer_date`, `contract_date`, `purchase_date`, `sale_date`) VALUES
	(1, 1441065600, 1441152000, 1441238400, 1442016000, 1442534400),
	(2, 1441152000, 1441152000, 1441324800, 1445817600, 1450137600),
	(3, 1441238400, 1441324800, 1441324800, 1444953600, 1451433600),
	(4, 1441324800, 1441324800, 1441324800, 1446768000, 1452816000),
	(5, 1441411200, 1441411200, 1441411200, 1448928000, 1454284800);

Edited September 18, 2015 by sptrsn

[Barand]

I am working on the query for, but, in the meantime I would advise you use DATE, DATETIME or TIMESTAMP type columns for date/times.

 

Not only are these immediately useful without conversion to a usable format, but you can tell what the the date is when you view your data, instead of an unintelligible string of digits

 

So while I am testing I am using DATE columns and inserted this data (to preserve your dates)

INSERT INTO `property` (`id`, `created`, `offer_date`, `contract_date`, `purchase_date`, `sale_date`) VALUES
	(1, FROM_UNIXTIME(1441065600), FROM_UNIXTIME(1441152000), FROM_UNIXTIME(1441238400), FROM_UNIXTIME(1442016000), FROM_UNIXTIME(1442534400)),
	(2, FROM_UNIXTIME(1441152000), FROM_UNIXTIME(1441152000), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1445817600), FROM_UNIXTIME(1450137600)),
	(3, FROM_UNIXTIME(1441238400), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1444953600), FROM_UNIXTIME(1451433600)),
	(4, FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1441324800), FROM_UNIXTIME(1446768000), FROM_UNIXTIME(1452816000)),
	(5, FROM_UNIXTIME(1441411200), FROM_UNIXTIME(1441411200), FROM_UNIXTIME(1441411200), FROM_UNIXTIME(1448928000), FROM_UNIXTIME(1454284800));

giving a readable 
+----+------------+------------+---------------+---------------+------------+
| id | offer_date | created    | contract_date | purchase_date | sale_date  |
+----+------------+------------+---------------+---------------+------------+
|  1 | 2015-09-02 | 2015-09-01 | 2015-09-03    | 2015-09-12    | 2015-09-18 |
|  2 | 2015-09-02 | 2015-09-02 | 2015-09-04    | 2015-10-26    | 2015-12-15 |
|  3 | 2015-09-04 | 2015-09-03 | 2015-09-04    | 2015-10-16    | 2015-12-30 |
|  4 | 2015-09-04 | 2015-09-04 | 2015-09-04    | 2015-11-06    | 2016-01-15 |
|  5 | 2015-09-05 | 2015-09-05 | 2015-09-05    | 2015-12-01    | 2016-02-01 |
+----+------------+------------+---------------+---------------+------------+

Edited September 18, 2015 by Barand

[Barand]

this is my query with my data

SELECT date
  , SUM(IF(type='CR',1,0)) as created
  , SUM(IF(type='OF',1,0)) as offers
  , SUM(IF(type='CO',1,0)) as contracts
  , SUM(IF(type='PU',1,0)) as purchases
  , SUM(IF(type='SA',1,0)) as sales
FROM
	(
	SELECT 'CR' as type, created as date FROM property
	UNION ALL
	SELECT 'OF' as type, offer_date as date FROM property
	UNION ALL
	SELECT 'CO' as type, contract_date as date FROM property
	UNION ALL
	SELECT 'PU' as type, purchase_date as date FROM property
	UNION ALL
	SELECT 'SA' as type, sale_date as date FROM property
    ) props
GROUP BY date;
+------------+---------+--------+-----------+-----------+-------+
| date       | created | offers | contracts | purchases | sales |
+------------+---------+--------+-----------+-----------+-------+
| 2015-09-01 |       1 |      0 |         0 |         0 |     0 |
| 2015-09-02 |       1 |      2 |         0 |         0 |     0 |
| 2015-09-03 |       1 |      0 |         1 |         0 |     0 |
| 2015-09-04 |       1 |      2 |         3 |         0 |     0 |
| 2015-09-05 |       1 |      1 |         1 |         0 |     0 |
| 2015-09-12 |       0 |      0 |         0 |         1 |     0 |
| 2015-09-18 |       0 |      0 |         0 |         0 |     1 |
| 2015-10-16 |       0 |      0 |         0 |         1 |     0 |
| 2015-10-26 |       0 |      0 |         0 |         1 |     0 |
| 2015-11-06 |       0 |      0 |         0 |         1 |     0 |
| 2015-12-01 |       0 |      0 |         0 |         1 |     0 |
| 2015-12-15 |       0 |      0 |         0 |         0 |     1 |
| 2015-12-30 |       0 |      0 |         0 |         0 |     1 |
| 2016-01-15 |       0 |      0 |         0 |         0 |     1 |
| 2016-02-01 |       0 |      0 |         0 |         0 |     1 |
+------------+---------+--------+-----------+-----------+-------+

[sptrsn]

Barand, 

Thanks so much. I can make this work. 

How would add a sum total as at the bottom?

Would you even do it in mysql?

If I'm doing this in a vacuum, I would probably use javascript just do it in the browser. 

And at which point would you convert from int to date format?

Edited September 19, 2015 by sptrsn

[Barand]

You can use "WITH ROLLUP" to get the overall totals. Add it at the end of the quey

. . .
. . .
GROUP BY date WITH ROLLUP

gives

+------------+---------+--------+-----------+-----------+-------+
| date       | created | offers | contracts | purchases | sales |
+------------+---------+--------+-----------+-----------+-------+
| 2015-09-01 |       1 |      0 |         0 |         0 |     0 |
| 2015-09-02 |       1 |      2 |         0 |         0 |     0 |
| 2015-09-03 |       1 |      0 |         1 |         0 |     0 |
| 2015-09-04 |       1 |      2 |         3 |         0 |     0 |
| 2015-09-05 |       1 |      1 |         1 |         0 |     0 |
| 2015-09-12 |       0 |      0 |         0 |         1 |     0 |
| 2015-09-18 |       0 |      0 |         0 |         0 |     1 |
| 2015-10-16 |       0 |      0 |         0 |         1 |     0 |
| 2015-10-26 |       0 |      0 |         0 |         1 |     0 |
| 2015-11-06 |       0 |      0 |         0 |         1 |     0 |
| 2015-12-01 |       0 |      0 |         0 |         1 |     0 |
| 2015-12-15 |       0 |      0 |         0 |         0 |     1 |
| 2015-12-30 |       0 |      0 |         0 |         0 |     1 |
| 2016-01-15 |       0 |      0 |         0 |         0 |     1 |
| 2016-02-01 |       0 |      0 |         0 |         0 |     1 |
| NULL       |       5 |      5 |         5 |         5 |     5 |
+------------+---------+--------+-----------+-----------+-------+

 

And at which point would you convert from int to date format?

Back at the original insertion of the data.

 

That table has more of a spreadsheet flavour than a db table. Is it created by collecting dates from other tables?