CODEWITHSUNDEEP
sqlsql-server
Tom Steadman
Tom Steadman

Reputation: 11

How do I show the percentage of a group in SQL?

I am trying to show the percentage of tasks completed before a certain deadline, for separate groups.

I have calculated the percentage for the whole, but when I try to split the data into a calculation for each group I keep getting errors.

To calculate the percentage, I have been using:

    DECLARE @TM DATETIME;
    SET @TM = DATEADD(MONTH,DATEDIFF(MONTH,'19000101', GETDATE()), '19000101')

    SELECT CAST(
        (
        SELECT COUNT (a.[completed]) 
        FROM [table] a 
        JOIN [other table] b 
        ON a.TaskID = b.TaskID 
        WHERE a.[completed] >= DATEADD(MONTH,0,@TM) AND 
              b.[completedByDeadline] = 1) AS DECIMAL (10,2))
            /
        CAST(
        (
        SELECT COUNT([completed]) 
        FROM [table]
        WHERE [completed] >= DATEADD(MONTH,0,@TM))
        *100 AS Decimal (10,2))

When I try to add the [groupName] column to the SELECT list and a GROUP BY clause, there are errors; it mentions the need for an EXISTS keyword, but I can't see where to put one.

Any help that people could provide would be fantastic! Thanks.

Upvotes: 0

Views: 56

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I like using AVG() for this sort of query:

SELECT AVG(case when b.[completedByDeadline] = 1 then 1.0 else 0 
           end) 
FROM [table] a LEFT JOIN
     [other table] b 
      ON a.TaskID = b.TaskID 
WHERE a.[completed] >= DATEADD(MONTH, 0, @TM);

If completedByDeadline only takes on the values 0 and 1, this can be simplified further:

SELECT AVG(b.[completedByDeadline] * 1.0)
FROM [table] a LEFT JOIN
     [other table] b 
      ON a.TaskID = b.TaskID 
WHERE a.[completed] >= DATEADD(MONTH, 0, @TM);

Note that these will return NULL if there are no corresponding tasks in b.

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

I would just use this:

DECLARE @TM DATETIME;
SET @TM = DATEADD(MONTH,DATEDIFF(MONTH,'19000101', GETDATE()), '19000101')

SELECT 
    COUNT (case 
               when b.[completedByDeadline] = 1 
                   then 1 
               else NULL 
           end) / COUNT (a.[completed])
FROM [table] a 
    LEFT JOIN [other table] b 
        ON a.TaskID = b.TaskID 
WHERE a.[completed] >= DATEADD(MONTH,0,@TM)

Upvotes: 1

Related Questions