SQL Server : Case Statement in function doesn't output correctly-CodePudding

I want to create a function to shorten the query in below

Query:

SELECT 
    CASE 
        WHEN SUBS_ARPU_M1 IS NULL 
            THEN 'Missing'
        WHEN SUBS_ARPU_M1 > 0 AND SUBS_ARPU_M1 <= 5 
            THEN '> 0 - <= 5'   
        WHEN SUBS_ARPU_M1 > 5 AND SUBS_ARPU_M1 <= 12 
            THEN '> 5 - <= 12'
        WHEN SUBS_ARPU_M1 > 12 AND SUBS_ARPU_M1 <= 25 
            THEN '> 12 - <=25'
        WHEN SUBS_ARPU_M1 > 25 
            THEN '> 25'
    END AS ARPU_GRP, 
    COUNT(*) 'Toplam Müşteri Sayısı'
FROM
    TRAIN
GROUP BY 
    CASE 
        WHEN SUBS_ARPU_M1 IS NULL 
            THEN 'Missing'
        WHEN SUBS_ARPU_M1 > 0 AND SUBS_ARPU_M1 <= 5 
            THEN '> 0 - <= 5'   
        WHEN SUBS_ARPU_M1 > 5 AND SUBS_ARPU_M1 <= 12 
            THEN '> 5 - <= 12'
        WHEN SUBS_ARPU_M1 > 12 AND SUBS_ARPU_M1 <= 25 
            THEN '> 12 - <=25'
        WHEN SUBS_ARPU_M1 > 25 
            THEN '> 25'
    END

OUTPUT:

ARPU_GRP    Toplam Müşteri Sayısı
----------------------------------
> 5 - <=12  1565
> 0 - <=5   1318
> 25        678
Missing     255
> 12 - <=25 1184

But when I create a function from the case statement part of the query above, output changes. Normally this function always works when I use between and method, but not with this one.

Function:

CREATE FUNCTION ARPU_GRP
    (@Arpu INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN
        CASE 
            WHEN @Arpu IS NULL THEN 'Missing'
            WHEN @Arpu > 0 AND @Arpu <= 5 THEN '> 0 - <=5'  
            WHEN @Arpu > 5 AND @Arpu <= 12 THEN '> 5 - <=12'
            WHEN @Arpu > 12 AND @Arpu <= 25 THEN '> 12 - <=25'
            WHEN @Arpu > 25 THEN '> 25'
        END 
END

Query:

SELECT 
    dbo.ARPU_GRP(SUBS_ARPU_M1) AS ARPU_GRP, 
    COUNT(*) 'Toplam Müşteri Sayısı'
FROM
    TRAIN
GROUP BY 
    dbo.ARPU_GRP(SUBS_ARPU_M1)

Output:

ARPU_GRP    Toplam Müşteri Sayısı
----------------------------------
NULL        208
> 0 - <=5   1392
> 12 - <=25 1053
> 25        645
> 5 - <=12  1447
Missing     255

CodePudding user response：

Why not just use a derived table?

WITH CTE AS(
    SELECT CASE WHEN SUBS_ARPU_M1 IS NULL THEN 'Missing'
                WHEN SUBS_ARPU_M1 > 0
                 AND SUBS_ARPU_M1 <= 5 THEN '> 0 - <=5'
                WHEN SUBS_ARPU_M1 > 5
                 AND SUBS_ARPU_M1 <= 12 THEN '> 5 - <=12'
                WHEN SUBS_ARPU_M1 > 12
                 AND SUBS_ARPU_M1 <= 25 THEN '> 12 - <=25'
                WHEN SUBS_ARPU_M1 > 25 THEN '> 25'
           END AS ARPU_GRP,
           YourIDColumn
    FROM dbo.TRAIN)
SELECT ARPU_GRP,
       COUNT(YourIDColumn) AS [ToplamMüşteriSayısı] --Don't use literal strings for aliases
FROM CTE
GROUP BY ARPU_GRP;

You can, however, make your CASE expression even more succinct by reversing the order of the boolean expressions and just checking the value is greater than the target:

WITH CTE AS(
    SELECT CASE WHEN SUBS_ARPU_M1 > 25 THEN '> 25'
                WHEN SUBS_ARPU_M1 > 12 THEN '> 25 - <= 25'
                WHEN SUBS_ARPU_M1 > 5 THEN '> 5 - <= 12'
                WHEN SUBS_ARPU_M1 > 0 THEN '> 0 - <=5'
                WHEN SUBS_ARPU_M1 IS NULL THEN 'Missing'
           END AS ARPU_GRP,        
           YourIDColumn
    FROM dbo.TRAIN)
SELECT ARPU_GRP,
       COUNT(YourIDColumn) AS [ToplamMüşteriSayısı] --Don't use literal strings for aliases
FROM CTE
GROUP BY ARPU_GRP;

CodePudding user response：

If you really want this as a function, I'd advise you to make this an inline Table-valued Function (of the form RETURNS TABLE AS RETURN SELECT...). It will perform far better than a scalar function. You can add it to your query using CROSS APPLY.

Taking @Larnu's existing excellent answer:

CREATE OR ALTER FUNCTION dbo.ARPU_GRP
(
    @SUBS_ARPU_M1 int
)
RETURNS TABLE
AS RETURN
    SELECT CASE WHEN @SUBS_ARPU_M1 > 25 THEN '> 25'
                WHEN @SUBS_ARPU_M1 > 12 THEN '> 25 - <= 25'
                WHEN @SUBS_ARPU_M1 > 5 THEN '> 5 - <= 12'
                WHEN @SUBS_ARPU_M1 > 0 THEN '> 0 - <=5'
                WHEN @SUBS_ARPU_M1 IS NULL THEN 'Missing'
           END AS ARPU_GRP;
GO

SELECT
  grp.ARPU_GRP,
  COUNT(tr.YourIDColumn) AS [ToplamMüşteriSayısı]
FROM dbo.TRAIN tr
CROSS APPLY dbo.ARPU_GRP(SUBS_ARPU_M1) grp
GROUP BY grp.ARPU_GRP;

Note that COUNT(SomeNonNullValue) is the same as COUNT(*), so you may want to rethink that. For clarity, it will not count distinct values, for that you would need COUNT(DISTINCT SomeNonNullValue).