CODEWITHSUNDEEP
sql
mprobus
mprobus

Reputation: 15

Count Records Between Values

I have the below query that pulls in max and average cpu and memory values for each server. 

SELECT
    Nodes.Caption, Max(CPULoad.MaxLoad) as CPUMax,
    AVG(CPULoad.AvgLoad) as CPUAvg,
    Round(Max(CPULoad.MaxMemoryUsed / CPULoad.TotalMemory * 100),2) as MemMax, 
    Round(AVG(CPULoad.AvgPercentMemoryUsed),2) as MEMAvg
FROM            
    Nodes 
INNER JOIN
    CPULoad ON Nodes.NodeID = CPULoad.NodeID
WHERE
    Datetime >= DATEADD(MONTH,datediff(MONTH,0,getdate())-1,0)        
    AND Datetime < DATEADD(MONTH,datediff(MONTH,0,getdate()),0)

GROUP BY Nodes.Caption

Now I need to figure out the following.

A count of each value (cpumax, cpuavg, memmax, memavg) that are between the following ranges:

  1. '>= 0 <= 10'
  2. '>= 10 <= 20'
  3. '>= 20 <= 30'
  4. '>= 30 <= 40'
  5. '>= 40 <= 50'

I tried something like SUM(case when Max(CPULoad.MaxLoad) < 10 then 1 else 0 end) but I keep getting an error

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

I tried to add both the entire statement and just the max(cpuload.maxload) in a GROUP BY, but it doesn't like it.

Lastly, I need to group each of the servers by the specific ranges. I.e. all servers that have a cpumax between 0 and 10 along with their corresponding value.

Upvotes: 0

Views: 33

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

try with the below query.. haven't tested yet :)

DECLARE @table TABLE( MinVal INT,MaxVal INT)
 INSERT INTO @table (MinVal,MaxVal)
VALUES  (0,10),(10,20),(20,30),(30,40),(40,50)

WITH cte_1
as
  (SELECT
    Nodes.Caption, Max(CPULoad.MaxLoad) as CPUMax,
    AVG(CPULoad.AvgLoad) as CPUAvg,
    Round(Max(CPULoad.MaxMemoryUsed / CPULoad.TotalMemory * 100),2) as MemMax, 
    Round(AVG(CPULoad.AvgPercentMemoryUsed),2) as MEMAvg

    FROM            
    Nodes INNER JOIN
          CPULoad ON Nodes.NodeID = CPULoad.NodeID

    WHERE
    Datetime >= DATEADD(MONTH,datediff(MONTH,0,getdate())-1,0)        AND 
    Datetime < DATEADD(MONTH,datediff(MONTH,0,getdate()),0) 

    GROUP BY Nodes.Caption)
    SELECT *, ISNULL('>= '+CAST( t.MinVal as VARCHAR(50))+' < '+CAST( t.MaxVal as VARCHAR(50)),'') CPUMaxInterval
            , ISNULL('>= '+CAST( t1.MinVal as VARCHAR(50))+' < '+CAST( t1.MaxVal as VARCHAR(50)),'') CPUAvgInterval
            , ISNULL('>= '+CAST( t2.MinVal as VARCHAR(50))+' < '+CAST( t2.MaxVal as VARCHAR(50)),'') MemMaxInterval
            , ISNULL('>= '+CAST( t3.MinVal as VARCHAR(50))+' < '+CAST( t3.MaxVal as VARCHAR(50)),'') MEMAvgInterval
    FROM cte_1 ct
     LEFT JOIN @table t on ct.CPUMax >= t.MinVal and ct.CPUMax <t.MaxVal
      LEFT JOIN @table t1 on ct.CPUAvg >= t.MinVal and ct.CPUAvg <t.MaxVal
       LEFT JOIN @table t2 on ct.MemMax >= t.MinVal and ct.MemMax <t.MaxVal
        LEFT JOIN @table t3 on ct.MEMAvg >= t.MinVal and ct.MEMAvg <t.MaxVal

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Make your existing query a derived table by making it a subquery with an alias. Then use a case construct. Here is the general idea. You can fill out the details.

select case when 1 = 1 then 'true' else 'false' end alias1
from 
(your query goes here) alias2

Upvotes: 0

Related Questions