CODEWITHSUNDEEP
sqlsql-serverrangecase
123testing123
123testing123

Reputation: 77

SQL Server: Selecting range of values from a table and doing calculation on them

I have this table

enter image description here

which has 3 columns

Machine Name | memory | date

I would like to:

  1. Select average of those memory if memory values are certain ranges
  2. I want to know how frequent it hits that range

Here's my crack at it but it :

SELECT
    MachineName, 
    CASE 
       WHEN Memory >= 75 AND Memory < 85 
          THEN AVG(Memory) AS Memory75to85,
               COUNT((MachineName)) AS Frequency75to85,
       WHEN Memory >= 85 AND Memory < 95 
          THEN AVG(Memory) AS Memory85to95,
               COUNT((MachineName)) AS Frequency85to95 
       WHEN Memory >= 95 AND Memory <= 100
          THEN AVG(Memory) AS Memory95to100,
               COUNT((MachineName)) AS Frequency95to100
       ELSE
          0, CounterDateTime
    END
FROM 
    Table
GROUP BY 
    MachineName

But it doesn't seem to work. The column result I want is:

| Name | Memory75to85 | Frequency75to85 | Memory85to95 | Frequency85to95 | Memory95to100 | Frequency95to100 | date |

Anyone care to help?

Upvotes: 0

Views: 643

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

I think what you want to use here is conditional aggregation:

SELECT
    MachineName,
    CounterDateTime,
    AVG(CASE WHEN Memory >= 75 AND Memory < 85 THEN Memory END) AS Memory75to85,
    COUNT(CASE WHEN Memory >= 75 AND Memory < 85 THEN 1 END) AS Frequency75to85,
    AVG(CASE WHEN Memory >= 85 AND Memory < 95 THEN Memory END) AS Memory85to95,
    COUNT(CASE WHEN Memory >= 85 AND Memory < 95 THEN 1 END) AS Frequency85to95,
    AVG(CASE WHEN Memory >= 95 AND Memory <= 100 THEN Memory END) AS Memory95to100,
    COUNT(CASE WHEN Memory >= 95 AND Memory <= 100 THEN 1 END) AS Frequency95to100
FROM yourTable
GROUP BY
    MachineName,
    CounterDateTime;

One major problem with your current approach is that a CASE expression has to generate a single scalar value; it can't output two different values. So in the query above, we take conditional averages and counts based on the memory range to generate the output you want.

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can try using conditional aggregation like below -

Select  
   MachineName, 
   avg(CASE WHEN Memory >=75 AND Memory <85 THEN Memory end) AS Memory75to85,
   count(CASE WHEN Memory >=75 AND Memory <85 THEN MachineName end) AS Frequency75to85,
   avg(case when Memory >=85 AND Memory <95 THEN Memory end) AS Memory85to95,
   count(case when Memory >=85 AND Memory <95 THEN MachineName end) AS Frequency85to95 
   avg(case WHEN Memory >=95 AND Memory <=100 THEN Memory end) AS Memory95to100,
   count(case WHEN Memory >=95 AND Memory <=100 THEN MachineName end) AS Frequency95to100,
   cast(CounterDateTime as date) as Date
From Table
Group by MachineName,cast(CounterDateTime as date)

Upvotes: 0

Related Questions