英文:
Aliases in MySQL and do maths
问题
我需要计算用户的有效性以在报告中显示,我编写了以下语句:
SELECT COUNT(`call_id`) AS logged
FROM `tbl_calls`
WHERE `user_id_attended_by` = 24
AND YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND `fk_supplier_id` = 3
UNION ALL
SELECT COUNT(call_id) AS ACK
FROM `tbl_calls`
WHERE YEAR(`date_logged`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(`date_logged`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND `repaired_by` = 24
AND `fk_supplier_id` = 3
上述语句的输出如下所示:
我需要获取图片中两个总数的百分比,以添加到报告中以获取用户的绩效。我尝试创建一个临时表并将数据输入其中,但在xampp中无法使其正常工作。
英文:
I need to workout the effectiveness of a user to display in a report, i complied the following statement
SELECT COUNT(`call_id`) AS logged
FROM `tbl_calls`
WHERE `user_id_attended_by` = 24
AND YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND `fk_supplier_id` = 3
UNION ALL
SELECT COUNT(call_id) AS ACK
FROM `tbl_calls`
WHERE YEAR(`date_logged`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(`date_logged`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND `repaired_by` = 24
AND `fk_supplier_id` = 3
The output for the above motioned statement as follows,
I need to get the percentages of the two totals in the pictures to add it to a report to get the users performance, I tried to create a temp table and entering the data into it but could not get it working in xampp.
答案1
得分: 1
SELECT logged,
100*logged/(logged+ack) percent_logged,
ack,
100*ack/(logged+ack) percent_ack
FROM (
SELECT SUM(`user_id_attended_by` = 24) AS logged,
SUM(`repaired_by` = 24) AS ack
FROM `tbl_calls`
WHERE YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND `fk_supplier_id` = 3
) subquery
英文:
SELECT logged,
100*logged/(logged+ack) percent_logged,
ack,
100*ack/(logged+ack) percent_ack
FROM (
SELECT SUM(`user_id_attended_by` = 24) AS logged,
SUM(repaired_by` = 24) AS ack
FROM `tbl_calls`
WHERE YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 1 YEAR)
AND MONTH(`date_ack_by_tech`) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND `fk_supplier_id` = 3
) subquery
?
答案2
得分: 1
我不知道date_ack_by_tech
和date_logged
之间的区别是什么,但我假设你使用它们有原因。所以,我没有尝试将你的查询合并在一起。如果不需要区分这两个日期,那么Akina的方法更可取。
我建议放弃使用MONTH()
和YEAR()
,而是使用日期范围。这将使这些条件成为可搜索谓词,这在这种情况下可能有帮助,也消除了当前的一月问题(2023年1月返回2022年12月,而2023年2月返回2022年1月 - 提前一个月或13个月)。
如果您想继续使用MONTH()
和YEAR()
的方法,您应该将年份标准更改为:
YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 13 MONTH)
英文:
I have no idea what the difference is between date_ack_by_tech
and date_logged
, but I assume you have a reason for using them. So, I have not tried to smudge your queries together. If there is no need to differentiate between these two dates, then Akina's method is preferable.
I would drop the use of MONTH() and YEAR() and instead use a date range. This will make these criteria sargable, which may or may not be helpful in this case. But it also does away with your current January issue (Jan 2023 returns Dec 2022, whereas Feb 2023 returns Jan 2022 - 1 month or 13 months).
SELECT (
SELECT COUNT(`call_id`) AS logged
FROM `tbl_calls`
WHERE `date_ack_by_tech` BETWEEN CURRENT_DATE - INTERVAL 13 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY
AND CURRENT_DATE - INTERVAL 12 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY - INTERVAL 1 SECOND
AND `user_id_attended_by` = 24
AND `fk_supplier_id` = 3
) AS logged, (
SELECT COUNT(call_id) AS ack
FROM `tbl_calls`
WHERE `date_logged` BETWEEN CURRENT_DATE - INTERVAL 13 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY
AND CURRENT_DATE - INTERVAL 12 MONTH - INTERVAL (DAY(CURRENT_DATE) - 1) DAY - INTERVAL 1 SECOND
AND `repaired_by` = 24
AND `fk_supplier_id` = 3
) AS ack,
(SELECT ROUND(100 * logged / (logged + ack), 2)) AS percent_logged,
(SELECT ROUND(100 * ack / (logged + ack), 2)) AS percent_ack;
If you want to continue using your MONTH() and YEAR() method, you should change the year criterion to:
YEAR(`date_ack_by_tech`) = YEAR(CURRENT_DATE - INTERVAL 13 MONTH)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论