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:
Replace in the output NULL with Total
alias;
Automatically calculate this formula:
( [B]Number[/B]([I]value of 2th query[/I]) - [B]Number[/B]([I]value of 1th query[/I]) ) / [B]Number[/B]([I]value of 2th query[/I])
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
June 26, 2012, 10:23am
2
use COALESCE
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
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
June 26, 2012, 12:34pm
4
you have two queries, right?
let’s call them q1 and q2
join them like this –
SELECT ...
FROM ( [I]q1 goes here [/I]) AS q1
INNER
JOIN ( [I]q2 goes here [/I]) AS q2
ON q2.application = q1.application
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;