Jump to content

Complex query help

sptrsn

By sptrsn
in MySQL Help

 Share

Recommended Posts

sptrsn Member

sptrsn

Posted
Posted

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

post-76123-0-47059200-1442600733_thumb.jpg

Link to comment
Share on other sites
sptrsn Member

sptrsn

Posted
  • Author
Posted (edited)

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 by sptrsn
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted (edited)

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 by Barand
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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 |
+------------+---------+--------+-----------+-----------+-------+
Link to comment
Share on other sites
sptrsn Member

sptrsn

Posted
  • Author
Posted (edited)

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 by sptrsn
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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?

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.