force a ceiling to count(*) in sql query

Question

I am using a subquery to return a count as an integer value to my main query. This query is used to rebind an ASP.NET DataGrid and I have only two characters width available for this column. I want to restrict the width to two characters. So, I want to set a value of 99 when the count exceeds 99. I can't figure a way to do this? I can't see how to apply a case statement here.

SELECT
     MEMB_ID,
     MEMB_Name,
     SELECT COUNT(*)
 FROM SessionOrder
WHERE SessionOrder.SORD_MEMB_ID = m.MEMB_ID 
  And SessionOrder.SORD_NumberCompleteDownloads <> 0
     As MEMB_Downloads,
     MEMB_JoinDate
FROM Member

How can this be done?

Answer 1

Replace

COUNT(*)

With

CASE WHEN COUNT(*) > 99 THEN 99 ELSE COUNT(*) END AS YourColumnName

Answer 2

The CASE expression can look like this:

CASE WHEN COUNT(*) > 99 THEN 99 ELSE COUNT(*) END

There appear to be a couple of errors with your existing query (for example m is not defined). With these errors corrected and the above change made the resulting query could look like this:

SELECT
     MEMB_ID,
     MEMB_Name,
     (
         SELECT CASE WHEN COUNT(*) > 99 THEN 99 ELSE COUNT(*) END
         FROM SessionOrder
         WHERE SessionOrder.SORD_MEMB_ID = MEMB_ID 
         AND SessionOrder.SORD_NumberCompleteDownloads <> 0
     ) AS MEMB_Downloads,
     MEMB_JoinDate
FROM Member

Answer 3

CASE it should be ...

Answer 4

or double UNION as SELECT MEMB_ID, MEMB_Name, SELECT COUNT(*) AS WC FROM SessionOrder WHERE SessionOrder.SORD_MEMB_ID = m.MEMB_ID And SessionOrder.SORD_NumberCompleteDownloads <> 0 And WC =< 99 As MEMB_Downloads, MEMB_JoinDate FROM Member UNION SELECT MEMB_ID, MEMB_Name, 99 AS WC FROM SessionOrder WHERE SessionOrder.SORD_MEMB_ID = m.MEMB_ID And SessionOrder.SORD_NumberCompleteDownloads <> 0 And WC > 99 As MEMB_Downloads, MEMB_JoinDate FROM Member

Answer 5

This might be a bit more efficient. As it can stop scanning rows once the 99th is reached.

SELECT MEMB_ID  ,
       MEMB_Name,
       ( SELECT COUNT(*)
       FROM    ( 
               SELECT TOP 99 * 
               FROM SessionOrder
               WHERE   SessionOrder.SORD_MEMB_ID = MEMB_ID
               AND     SessionOrder.SORD_NumberCompleteDownloads <> 0
               )
               Top99
       ) AS MEMB_Downloads,
       MEMB_JoinDate
FROM   Member

Answer 6

Rather than change the COUNT(*) result, better count at most 99:

SELECT
     MEMB_ID,
     MEMB_Name,
     (SELECT COUNT(*)
       FROM (
        SELECT TOP(99) *
        FROM SessionOrder
        WHERE SessionOrder.SORD_MEMB_ID = m.MEMB_ID 
        And SessionOrder.SORD_NumberCompleteDownloads <> 0)
       as TOP99_Downloads)
     As MEMB_Downloads,
     MEMB_JoinDate
FROM Member;

This way you avoid counting all the downloads when you'll only display 99 anyway. Of course, one would ask what is the point of displaying a value if is incorrect to start with and why not make your UI layer capable of displaying 'more than 99'.