Query to convert the count to million or thousands

I am running a query on a database to return count(*) for number of rows.

• If the count is greater than million say 2,250,304, I want the query to return 2.25M.
• If the count is greater than 1000 but less than a million say 65130 I want the query to return ‘65.1k’.
• If the count is less than 1000 then the query should return actual count.

Here is my query:

SELECT DATENAME(mm, CREATEDTS) + ', ' + DATENAME(yyyy, CREATEDTS) AS Month,
       CASE
         WHEN count(*) > 1000
              AND count(*) < 1000000 THEN ( CONVERT (VARCHAR(10), ( count(*) / 1000 )) ) + 'k'
         WHEN count(*) > 1000000 THEN ( CONVERT (VARCHAR(10), ( count(*) / 1000 )) ) + 'm'
         ELSE COUNT(*)
       END
FROM   USAGEDATA
GROUP  BY month(CREATEDTS),
          DATENAME(mm, CREATEDTS) + ', ' + DATENAME(yyyy, CREATEDTS)
ORDER  BY MONTH(CREATEDTS) 

When I run it I am getting an error:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting
the varchar value ‘165k’ to data type int.

I am not sure where I am converting varchar to INT and getting this error.

This type of formatting is generally best done in your application if possible.

The problem is that the case expression returns a result based on the highest datatype precedence of any branch.

So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar.

Also you should probably add year into your order by except if you actually want to order Jan 2014 and Jan 2015 together followed by Feb and so on.

Though I might well be minded to take that concatenated string out of the GROUP BY too and rewrite it (Along the way fixing a couple of other issues with integer division and inconsistent boundary conditions) as.

WITH T(mmmyyyy, FORMATTED_COUNT, Yr, Mnth)
     AS (SELECT DATENAME(MONTH, MIN(CREATEDTS)) + ', ' + DATENAME(YEAR, MIN(CREATEDTS)) AS Month,
                CASE
                  WHEN COUNT(*) BETWEEN 1000 AND 999999
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000.0, 1) AS NUMERIC(4, 1)) )) ) + 'k'
                  WHEN COUNT(*) >= 1000000
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000000.0, 2) AS NUMERIC(6, 2)) )) ) + 'm'
                  ELSE CONVERT (VARCHAR(10), COUNT(*))
                END,
                YEAR(CREATEDTS),
                MONTH(CREATEDTS)
         FROM   USAGEDATA
         GROUP  BY YEAR(CREATEDTS),
                   MONTH(CREATEDTS))
SELECT mmmyyyy,
       FORMATTED_COUNT
FROM   T
ORDER  BY Yr,
          Mnth;