Make ceiling count (*) in sql query

Question

I use a subquery to return the invoice as an integer value in my main query. This query is used to overwrite an ASP.NET DataGrid, and there is only two character widths for this column. I want to limit the width to two characters. So, I want to set the value to 99 when the quantity exceeds 99. I can not figure out how to do this? I do not see how to apply the case argument 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 I do that?

Answer 1

A CASE expression might look like this:

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

, (, m ). , :

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 2

COUNT(*)

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

Answer 3

. 99- .

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 4

, COUNT (*), 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;

, , 99. , , , , " 99".

Answer 5

CASE ...

Answer 6

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