SQL-计算多个计数的百分比

2024-05-18 • 问答

我写了一个查询来按状态对db中的所有项目进行计数和分组。

现在，我需要计算每个给定结果的百分比。

func removeAllAnnotations() {
    guard let document = pdfView.document else { return }

    for index in 0..<document.pageCount {
        if let page = document.page(at: index) {
            let annotations = page.annotations
            for annotation in annotations {
                page.removeAnnotation(annotation)
            }
        }
    }
}

func searchBar(_ searchBar: UISearchBar,textDidChange searchText: String) {
    removeAllAnnotations()
    return highlight(searchTerms: [searchText])
}

这给了我

enter image description here

我尝试了以下方法：

    SELECT 
      COUNT(CASE WHEN status='Pending' THEN 1 ELSE NULL END)  as 'Pennding Requests',COUNT(CASE WHEN status='accepted' THEN 1 ELSE NULL END) as 'accepted Requests',COUNT(CASE WHEN status='Denied' THEN 1 ELSE NULL END) as 'Denied Requests'
    FROM meeting_request;

有人可以建议如何计算这三种状态的百分比。

jerrytoto 回答：SQL-计算多个计数的百分比

您可以使用AVG()：

SELECT AVG( status = 'Pending' )  as pending_requests,AVG( status = 'Accepted' ) as Accepted_Requests,AVG( status = 'Denied' ) as Denied_Requests
FROM meeting_request;

由于MySQL将布尔值解释为数字，其中1表示true，0表示false，因此可以使用方便的快捷方式。更正式的SQL代码是：

SELECT AVG(CASE WHEN status = 'Pending' THEN 1.0 ELSE 0 END)

您尝试过这样做吗？

    SELECT 
      COUNT(CASE WHEN status='Pending' THEN 1 ELSE NULL END) / SUM(1) * 100.0  as 'Pennding Requests Percent',COUNT(CASE WHEN status='Accepted' THEN 1 ELSE NULL END) / SUM(1) * 100.0 as 'Accepted Requests Percent',COUNT(CASE WHEN status='Denied' THEN 1 ELSE NULL END) / SUM(1) * 100.0 as 'Denied Requests Percent'
    FROM meeting_request;

mysql percentage sql

本文链接：https://www.f2er.com/2962814.html

SQL-计算多个计数的百分比

jerrytoto 回答：SQL-计算多个计数的百分比

大家都在问