Query union ?

[Miguel61]

Hi everyone and good day.

I have two queries for this output:

+--------------+-------------+------------+--------+-------------+-------------------+
| application  | StartDate   | EndDate    | Number | Number days | Number for day    |
+--------------+-------------+------------+--------+-------------+-------------------+
| NULL         | 01/01/2011  | 26/06/2011 |  58348 |   176       | 332               |
| Report       | 01/01/2011  | 26/06/2011 |   6987 |   176       | 40                |
+--------------+-------------+------------+--------+-------------+-------------------+


+--------------+-------------+------------+--------+-------------+-------------------+
| application  | StartDate   | EndDate    | Number | Number days | Number for day    |
+--------------+-------------+------------+--------+-------------+-------------------+
| NULL         | 01/01/2012  | 26/06/2012 |  56937 |   177       | 322               |
| Report       | 01/01/2012  | 26/06/2012 |   6314 |   177       | 36                |
+--------------+-------------+------------+--------+-------------+-------------------+

I need:

1. Replace in the output NULL with `Total` alias;
2. Automatically calculate this formula:
   

   ( Number(value of 2th query) - Number(value of 1th query) ) / Number(value of 2th query)

   e.g.: ( 56937 - 58348 ) / 58348 ====> -0.0241

How to with only query?
Many thanks for your help.

mysql> SELECT
	*
FROM
	(
		SELECT
			application,
			DATE_FORMAT(
				MAKEDATE(YEAR(CURDATE()) - 1, 1),
				'%d/%m/%Y'
			) `StartDate`,
			DATE_FORMAT(
				DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
				'%d/%m/%Y'
			) `EndDate`,
			COUNT(*) `Number`,
			DATEDIFF(
				DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
				MAKEDATE(YEAR(CURDATE()) - 1, 1)
			) `Number days`,
			FORMAT(
				(
					COUNT(*) / DATEDIFF(
						DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
						MAKEDATE(YEAR(CURDATE()) - 1, 1)
					)
				),
				0
			) `Number for day`
		FROM
			tbl_backup
		WHERE
			1
		AND application NOT LIKE 'home page'
		AND `myDate` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
		AND DATE_ADD(CURDATE(), INTERVAL - 1 YEAR)
		GROUP BY
			application WITH ROLLUP
	) t
ORDER BY
	t.`Number` DESC
LIMIT 1;



SELECT
	*
FROM
	(
		SELECT
			application,
			DATE_FORMAT(
				MAKEDATE(YEAR(CURDATE()), 1),
				'%d/%m/%Y'
			) `StartDate`,
			DATE_FORMAT(CURDATE(), '%d/%m/%Y') `EndDate`,
			COUNT(*) `Number`,
			DATEDIFF(
				CURDATE(),
				MAKEDATE(YEAR(CURDATE()), 1)
			) `Number days`,
			FORMAT(
				(
					COUNT(*) / DATEDIFF(
						CURDATE(),
						MAKEDATE(YEAR(CURDATE()), 1)
					)
				),
				0
			) `Number for day`
		FROM
			tbl_backup
		WHERE
			1
		AND application NOT LIKE 'home page'
		AND `myDate` BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
		AND CURDATE()
		GROUP BY
			application WITH ROLLUP
	) t
ORDER BY
	t.`Number` DESC
LIMIT 1;

[r937]

Replace in the output NULL with `Total` alias;

use COALESCE

Automatically calculate this formula:

this is not possible in one query with UNION

what you will have to do is a join between your two queries, matching them on application

i'm surprised that you're using LIMIT 1 if you have GROUP BY application

also, a tip -- you can replace DATEDIFF(CURDATE(),MAKEDATE(YEAR(CURDATE()),1)) with DAYOFYEAR(CURRENT_DATE)-1

[Miguel61]

Thank you my friend for your tips.

I modify my queries and I have my first correct output.
I use LIMIT 1 to simplify the sql query...

I'm sorry but I don't understand this suggestion:

what you will have to do is a join between your two queries, matching them on application

SELECT
	* 
FROM
	(
		SELECT
                        COALESCE(application,'Sum') application,
			DATE_FORMAT(
				MAKEDATE(YEAR(CURDATE()) - 1, 1),
				'%d/%m/%Y'
			) `StartDate`,
			DATE_FORMAT(
				DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
				'%d/%m/%Y'
			) `EndDate`,
			COUNT(*) `Number`,
			DATEDIFF(
				DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
				MAKEDATE(YEAR(CURDATE()) - 1, 1)
			) `Nr of days`,
			FORMAT(
				(
					COUNT(*) / DATEDIFF(
						DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
						MAKEDATE(YEAR(CURDATE()) - 1, 1)
					)
				),
				0
			) `Nr for days`
		FROM
			tbl_backup
		WHERE
			1
		AND application NOT LIKE 'home page'
		AND myDate BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
		AND DATE_ADD(CURDATE(), INTERVAL - 1 YEAR)
		GROUP BY
			application WITH ROLLUP
	) t
ORDER BY
	t.`Number` DESC;

SELECT
	*
FROM
	(
		SELECT
			COALESCE(application,'Sum') application,
			DATE_FORMAT(
				MAKEDATE(YEAR(CURDATE()), 1),
				'%d/%m/%Y'
			) `StartDate`,
			DATE_FORMAT(CURDATE(), '%d/%m/%Y') `EndDate`,
			COUNT(*) `Number`,
			DAYOFYEAR(CURRENT_DATE) - 1 `Nr of days`,
			FORMAT(
				(
					COUNT(*) / DAYOFYEAR(CURRENT_DATE) - 1
				),
			0
	) `Nr for days`
FROM
	tbl_backup
WHERE
	1
AND application NOT LIKE 'home page'
AND myDate BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND CURDATE()
GROUP BY
	application WITH ROLLUP
) t
ORDER BY
	t.`Number` DESC;

[r937]

I'm sorry but I don't understand this suggestion:

what you will have to do is a join between your two queries, matching them on application

you have two queries, right?

let's call them q1 and q2

join them like this --

SELECT ...
  FROM ( q1 goes here ) AS q1
INNER
  JOIN ( q2 goes here ) AS q2
    ON q2.application = q1.application

[Miguel61]

GREAT !

Thanks so much!

SELECT
	q1.Application,
	q1.`Number` q1,
	q2.`Number` q2,
	FORMAT(
		(
			(
				q2.`Number` - q1.`Number`
			) / q1.`Number`
		) * 100,
		1
	) `perc`
FROM
	(
		SELECT
			COALESCE(application, 'Sum') Application,
			DATE_FORMAT(
				MAKEDATE(YEAR(CURDATE()) - 1, 1),
				'%d/%m/%Y'
			) `StartDate`,
			DATE_FORMAT(
				DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
				'%d/%m/%Y'
			) `EndDate`,
			COUNT(*) `Number`,
			DATEDIFF(
				DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
				MAKEDATE(YEAR(CURDATE()) - 1, 1)
			) `Nr of days`,
			FORMAT(
				(
					COUNT(*) / DATEDIFF(
						DATE_ADD(CURDATE(), INTERVAL - 1 YEAR),
						MAKEDATE(YEAR(CURDATE()) - 1, 1)
					)
				),
				0
			) `Nr for day`
		FROM
			tbl_backup
		WHERE
			1
		AND application NOT LIKE 'home page'
		AND `myDate` BETWEEN MAKEDATE(YEAR(CURDATE()) - 1, 1)
		AND DATE_ADD(CURDATE(), INTERVAL - 1 YEAR)
		GROUP BY
			application WITH ROLLUP
	) q1
INNER JOIN (
	SELECT
		COALESCE(application, 'Sum') Application,
		DATE_FORMAT(
			MAKEDATE(YEAR(CURDATE()), 1),
			'%d/%m/%Y'
		) `StartDate`,
		DATE_FORMAT(CURDATE(), '%d/%m/%Y') `EndDate`,
		COUNT(*) `Number`,
		DAYOFYEAR(CURRENT_DATE) - 1 `Nr of days`,
		FORMAT(
			(
				COUNT(*) / DAYOFYEAR(CURRENT_DATE) - 1
			),
			0
		) `Nr for day`
	FROM
		tbl_backup
	WHERE
		1
	AND application NOT LIKE 'home page'
	AND `myDate` BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
	AND CURDATE()
	GROUP BY
		application WITH ROLLUP
) q2 ON q2.Application = q1.Application
ORDER BY
	q2.`Number` DESC;