SQL:使用聚合函数获取百分比的查询有问题 [英] SQL: Having trouble with query that gets percentages using aggregate functions

问题描述

我无论如何都不是 SQL 专家，并且很难从查询中获取我需要的数据.我正在处理一个表，Journal_Entry，它有很多列.一列是 Status_ID，它是 Status 表的外键，具有三个值Green"、Yellow"和Red".此外，日志条目是针对特定用户 (User_ID) 记录的.

I'm not an expert in SQL by any means, and am having a hard time getting the data I need from a query. I'm working with a single table, Journal_Entry, that has a number of columns. One column is Status_ID, which is a foreign key to a Status table with three values "Green", "Yellow", and "Red". Also, a journal entry is logged against a particular User (User_ID).

我正在尝试获取为每个状态记录的日记条目数，作为特定用户记录的日记条目总数的百分比.到目前为止，我已经获得了以下状态为 1 的状态，即绿色(我知道这不起作用):

I'm trying to get the number of journal entries logged for each Status, as a percentage of the total number of journal entries logged by a particular user. So far I've got the following for a Status of 1, which is green (and I know this doesn't work):

SELECT  CAST((SELECT COUNT(Journal_Entry_ID) 
        FROM Journal_Entry 
        WHERE Status_ID = 1 AND User_ID = 3 / 
        SELECT COUNT(Journal_Entry_ID) 
        FROM Journal_Entry AND User_ID = 3)) AS FLOAT * 100

我需要继续查询其他两个状态 ID，2 和 3，理想情况下希望以选择三列作为百分比结束，每个状态一列:Green_Percent"、Yellow_Percent"和"Red_Percent".

I need to continue the query for the other two status ID's, 2 and 3, and ideally would like to end with the selection of three columns as percentages, one for each Status: "Green_Percent", "Yellow_Percent", and "Red_Percent".

这可能是我问过的最杂乱无章的问题，因此对于任何不够明确的问题，我深表歉意.我很乐意在必要时进行澄清.另外，我使用的是 SQL Server 2005.

This is probably the most disjointed question I've ever asked, so I apologize for any lack of clarity. I'll be happy to clarify as necessary. Also, I'm using SQL Server 2005.

非常感谢.

推荐答案

使用:

  SELECT je.statusid,
         COUNT(*) AS num,
         (COUNT(*) / (SELECT COUNT(*)+.0
                       FROM JOURNAL_ENTRY) ) * 100
    FROM JOURNAL_ENTRY je
GROUP BY je.statusid

然后是格式化您想要的精度的问题:

Then it's a matter of formatting the precision you want:

CAST(((COUNT(*) / (SELECT COUNT(*)+.0 FROM BCCAMPUS.dbo.COURSES_RFIP)) * 100)
     AS DECIMAL(4,2))

...将给出两位小数.如果您不需要任何小数位，请将结果转换为 INT.

...will give two decimal places. Cast the result to INT if you don't want any decimal places.

您可以使用 CTE 来减少重复:

You could use a CTE to minimize the duplication:

WITH cte AS (
   SELECT je.*
     FROM JOURNAL_ENTRY je
    WHERE je.user_id = 3)
  SELECT c.statusid,
         COUNT(*) AS num,
         (COUNT(*) / (SELECT COUNT(*)+.0
                       FROM cte) ) * 100
    FROM cte c
GROUP BY c.statusid

这篇关于SQL:使用聚合函数获取百分比的查询有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！