Thread: Query union ?

1. Query union ?

Hi everyone and good day.

I have two queries for this output:
Code:
```+--------------+-------------+------------+--------+-------------+-------------------+
| 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:
Code:
`( 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.
Code:
```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 `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 `myDate` BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND CURDATE()
GROUP BY
application WITH ROLLUP
) t
ORDER BY
t.`Number` DESC
LIMIT 1;```

2. Originally Posted by Miguel61
Replace in the output NULL with `Total` alias;
use COALESCE

Originally Posted by Miguel61
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

3. 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
Code:
```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 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 myDate BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND CURDATE()
GROUP BY
application WITH ROLLUP
) t
ORDER BY
t.`Number` DESC;```

4. Originally Posted by Miguel61
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 --
Code:
```SELECT ...
FROM ( q1 goes here ) AS q1
INNER
JOIN ( q2 goes here ) AS q2
ON q2.application = q1.application```

5. GREAT !

Thanks so much!
Code:
```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 `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 `myDate` BETWEEN MAKEDATE(YEAR(CURDATE()), 1)
AND CURDATE()
GROUP BY
application WITH ROLLUP
) q2 ON q2.Application = q1.Application
ORDER BY
q2.`Number` DESC;```

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•