CODEWITHSUNDEEP
sqlsql-servert-sql
Tyler
Tyler

Reputation: 1050

Calculate Percentage with T-SQL statement while using GROUP BY

In working through the AdventureWorks2012 database for Microsoft SQL Server 2012 in this part of the tutorial, I wanted to add to the example and find out how use a variation of this query:

USE AdventureWorks2012;
GO
SELECT 
    DATEDIFF(dd,DueDate,EndDate) AS 'Days Late',
    COUNT(WorkOrderID) AS 'Late Orders',
    (COUNT(WorkOrderID) / SUM(COUNT(WorkOrderID)) AS '% of Late Orders'
 FROM Production.WorkOrder
 WHERE DueDate < EndDate
 GROUP BY DATEDIFF(dd,DueDate,EndDate)
 ORDER BY DATEDIFF(dd,DueDate,EndDate);

To make this table where '% of Late Orders' gives the count which was late by a certain number of days and divides it by all orders that were late, it should look like the following:

----------------------------------------
|Days Late|Late Orders|% of Late Orders|
----------------------------------------
|21       |784        |10              |
|18       |1285       |14              |
----------------------------------------

I've tried several variations of this query, viewed several related StackOverflow questions, but cannot get this table to work without receiving error messages. This question got me closest but the results were off.

Thank you for your help.

Upvotes: 1

Views: 290

Answers (2)

mwigdahl
mwigdahl

Reputation: 16588

The problem is that since you are using a GROUP BY statement, all of your aggregate functions are operating over your subgroups. You will need to separately calculate the value for "all orders that were late" in order to get the value over all late orders instead of all late orders with a given lateness, likely using a CTE.

Perhaps something like this:

USE AdventureWorks2012;
GO

;WITH late (num_late_orders)
AS
(
    SELECT COUNT(WorkOrderID)
    FROM Production.WorkOrder
    WHERE DueDate < EndDate
)
SELECT 
    DATEDIFF(dd,DueDate,EndDate) AS 'Days Late',
    COUNT(WorkOrderID) AS 'Late Orders',
    (CAST(COUNT(WorkOrderID) AS decimal(14, 3)) / CAST(MAX(late.num_late_orders) AS decimal(14, 3))) AS '% of Late Orders'
FROM Production.WorkOrder
    CROSS JOIN late
WHERE DueDate < EndDate
GROUP BY DATEDIFF(dd,DueDate,EndDate)
ORDER BY DATEDIFF(dd,DueDate,EndDate);

You might be confused as to why I have MAX() around late.num_late_orders. The reason is that since the value is not included in the GROUP BY clause, it has to be included in an aggregate statement, even though the value is constant over the entire query.

Upvotes: 2

neiha
neiha

Reputation: 171

Try with this:

SELECT  DATEDIFF(dd,DueDate,EndDate) AS 'Days Late',
COUNT(WorkOrderID) AS 'Late Orders',
(COUNT(WorkOrderID)*1.0 / (select count(1) as TotalLateOrders FROM Production.WorkOrder
WHERE DueDate < EndDate))
AS '% of Late Orders'
FROM Production.WorkOrder
WHERE DueDate < EndDate
GROUP BY DATEDIFF(dd,DueDate,EndDate)
ORDER BY DATEDIFF(dd,DueDate,EndDate);

I'm multiplying by 1.0 to divide with floats, you can do this on the previous answer too

Upvotes: 1

Related Questions