CODEWITHSUNDEEP
sqlsql-servert-sql
FrodoFraggins
FrodoFraggins

Reputation: 59

How to modify my code to set sum/avg to negative if any one value is negative

Below is my existing SQL code (sql server) that didn't check for error values. It simply calculates a sum column and an average column if the month and year values match.

SELECT ur.Domain, SUM(ur.DataUsageInBytes) as SumDataUsageInBytes, 
       AVG(ur.DiskUsageInBytes) as AvgDiskUsageInBytes
FROM UsageResults ur
WHERE (ur.Year = @year) AND (ur.Month = @month) 
GROUP BY [Domain]

I want to add logic such that if any ur.DataUsageInBytes value(for the given month/year) is negative I want to set the sum to say -99, otherwise I'll just compute the normal sum. The same goes for the average.

The table entries it is reading will have negative values if there was an error encountered and their values couldn't be determined.

Upvotes: 0

Views: 354

Answers (2)

Andomar
Andomar

Reputation: 238098

SELECT  ur.Domain
,       case 
        when min(ur.DataUsageInBytes) < 0 then -99
        else sum(ur.DataUsageInBytes) 
        end as SumDataUsageInBytes
,       case 
        when min(ur.DataUsageInBytes) < 0 then -99
        else avg(ur.DataUsageInBytes) 
        end as AvgDiskUsageInBytes
FROM    UsageResults ur
WHERE   ur.Year = @year AND ur.Month = @month
GROUP BY 
        ur.Domain

Upvotes: 3

Matt Busche
Matt Busche

Reputation: 14333

you could turn each value into a positive and compare sums

SELECT ur.Domain
, CASE WHEN SUM(ur.DataUsageInBytes) = SUM(ABS(ur.DataUsageInBytes)) THEN SUM(ur.DataUsageInBytes) ELSE -99 END as SumDataUsageInBytes
, CASE WHEN min(ur.DiskUsageInByters) < 0 THEN -99 ELSE AVG(ur.DiskUsageInBytes) END AS AvgDiskUsageInBytes
FROM UsageResults ur
WHERE (ur.Year = @year) AND (ur.Month = @month) 
GROUP BY [Domain]

Upvotes: 0

Related Questions