Clever Geek Handbook

SQL query to sum data value and count the number of times above 60

I am trying to query the following data. For each time period from the PrctBusy column, I want Max, Min, Average, total and number of times> 60.

Array           TimeStamp       Category Instance PrctBusy  QueUtil
000198701258    2016-07-01 00:00    FE_DIR  FA-1E   59      0
000198701258    2016-07-01 00:00    FE_DIR  FA-1F   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-1G   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-1H   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-2E   64      0
000198701258    2016-07-01 00:00    FE_DIR  FA-2F   10      0
000198701258    2016-07-01 00:00    FE_DIR  FA-2G   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-2H   10      0
000198701258    2016-07-01 00:00    FE_DIR  FA-3E   64      0
000198701258    2016-07-01 00:00    FE_DIR  FA-3F   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-3G   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-3H   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-4E   64      0
000198701258    2016-07-01 00:00    FE_DIR  FA-4F   10      0
000198701258    2016-07-01 00:00    FE_DIR  FA-4G   9       0
000198701258    2016-07-01 00:00    FE_DIR  FA-4H   9       0
000198701258    2016-07-01 00:05    FE_DIR  FA-1E   62      0
000198701258    2016-07-01 00:05    FE_DIR  FA-1F   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-1G   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-1H   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-2E   66      0
000198701258    2016-07-01 00:05    FE_DIR  FA-2F   13      0
000198701258    2016-07-01 00:05    FE_DIR  FA-2G   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-2H   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-3E   66      0
000198701258    2016-07-01 00:05    FE_DIR  FA-3F   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-3G   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-3H   12      0
000198701258    2016-07-01 00:05    FE_DIR  FA-4E   66      0
000198701258    2016-07-01 00:05    FE_DIR  FA-4F   13      0
000198701258    2016-07-01 00:05    FE_DIR  FA-4G   13      0
000198701258    2016-07-01 00:05    FE_DIR  FA-4H   11      0
000198701258    2016-07-01 00:10    FE_DIR  FA-1E   52      0
000198701258    2016-07-01 00:10    FE_DIR  FA-1F   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-1G   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-1H   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-2E   56      0
000198701258    2016-07-01 00:10    FE_DIR  FA-2F   13      0
000198701258    2016-07-01 00:10    FE_DIR  FA-2G   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-2H   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-3E   56      0
000198701258    2016-07-01 00:10    FE_DIR  FA-3F   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-3G   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-3H   12      0
000198701258    2016-07-01 00:10    FE_DIR  FA-4E   56      0
000198701258    2016-07-01 00:10    FE_DIR  FA-4F   13      0
000198701258    2016-07-01 00:10    FE_DIR  FA-4G   13      0
000198701258    2016-07-01 00:10    FE_DIR  FA-4H   11      0

I've tried several different queries. The one that is the closet for what I need is:

SELECT TimeStamp, 
    max(prctbusy) AS maxPrctBusy, 
    min(prctbusy) AS minPrctBusy, 
    AVG(prctbusy) AS avgPrctBusy, 
    count(prctbusy) AS numPrctBusy,  
  ( SELECT count(prctbusy) 
    FROM VMAXPortUtil 
    WHERE array like '%1258%' and Category like '%FE_DIR%' and prctbusy > 60
    ) AS above60 
FROM VMAXPortUtil
WHERE array like '%1258%' and Category like '%FE_DIR%' 
GROUP BY TimeStamp 
Order by TimeStamp

Unfortunately the result is not entirely correct and gives a total above 60 instead of each TimeStamp.

What I get:

TimeStamp       maxPrctBusy minPrctBusy avgPrctBusy numPrctBusy above60
2016-07-01 00:00    64      9           22          16          8494
2016-07-01 00:05    66      11          25          16          8494
2016-07-01 00:10    56      11          23          16          8494

What I need:

TimeStamp       maxPrctBusy minPrctBusy avgPrctBusy numPrctBusy above60
2016-07-01 00:00    64      9           22          16          3
2016-07-01 00:05    66      11          25          16          4
2016-07-01 00:10    56      11          23          16          0

I think that concatenation might be what I need and count the number above 60. But I didn't figure out how to get the number of result rows the same. Any suggestions would be appreciated.

+3


source to share


1 answer 


SELECT TimeStamp, 
    max(prctbusy) AS maxPrctBusy, 
    min(prctbusy) AS minPrctBusy, 
    AVG(prctbusy) AS avgPrctBusy, 
    count(prctbusy) AS numPrctBusy,
    sum(case when prctbusy > 60 then 1 else 0 end) AS above60
FROM VMAXPortUtil
WHERE array like '%1258%' and Category like '%FE_DIR%' 
GROUP BY TimeStamp 
Order by TimeStamp

This should do the trick, assuming you want numPrctBusy to stay.



You can add non-grouped fields inside aggregate functions, allowing comparison check

+1


source






More articles:


All Articles