CODEWITHSUNDEEP
sqlsql-server
user3266638
user3266638

Reputation: 449

Rounding and getting a count of the number of times a number appears? SQL

I have the following data:

-1.25
-0.5
   0
   0
1.25
0.75
1.25
   2
   2
   2
   2

I'm trying to consolidate these numbers while also rounding them, and making anything that is negative go to 0.

Here is what I have so far: 

SELECT 
    RawScore,
    Count(*) AS Freq
FROM TestScores
GROUP BY RawScore

This returns 

RawScore    Freq
-1.25        1
-0.5         1
   0         2
0.75         1
1.25         1
   2         4

Is there a way to get this instead?

RawScore    Freq
     0        4
     1        2
     2        4

Upvotes: 1

Views: 359

Answers (2)

Bijan Ghasemi
Bijan Ghasemi

Reputation: 296

SELECT 
    FLOOR(ABS(RawScore)),
    Count(*) AS Freq
FROM TestScores
GROUP BY FLOOR(ABS(RawScore))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can do:

select (case when rawScore < 0 then 0
             else round(rawScore, 0)
        end) as nolongerrawScore,
       count(*)
from TestScores
group by (case when rawScore < 0 then 0
               else round(rawScore, 0)
          end) 
order by nolongerrawScore;

Upvotes: 1

Related Questions