KlaasJan KlaasJan - 6 months ago 12
SQL Question

Building a scoreboard - need to combine the values of two different referrals into one total sum

I'm building a simple scoreboard. It's basically a summed up list of every employee in the company who brings in external users ("referrals"). The list is simply done by grouping on the last 3 letters of the referral (= the employees initials). However, one of the employees use two different referrals and I need to select and sum these two referrals into one total sum (so I can use that value to order by on my scoreboard).

A normal referral looks like this: "%_abc". The employee in question has two referrals: "%_xyz" and "RE%".

This is the current code:

SELECT
RIGHT(Referral, 3) [Referral], COUNT(*) [Total]
FROM TableName
WHERE
CAST(Created AS DATE) = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
AND
(
Referral LIKE '%_abc'
OR
Referral LIKE '%_xyz'
...
)
GROUP BY RIGHT(Referral, 3)
ORDER BY [Total] DESC

Answer

I solved my problem with the following code. Any optimisation tips are welcome.

SELECT Referral, SUM(Total) AS Total FROM
(
    SELECT RIGHT(Referral, 3) [Referral], COUNT(*) [Total]
    FROM TableName
    WHERE
           CAST(Created AS DATE) = CAST(GETDATE() AS DATE)
        AND
        (
            Referral LIKE '%_abc'
            OR
            Referral LIKE '%_xyz'
            ...
        )
    GROUP BY RIGHT(Referral, 3)
    UNION ALL
    SELECT 'xyz' AS Referral, COUNT(*) [Total]
    FROM TableName
    WHERE
        CAST(Created AS DATE) = CAST(GETDATE() AS DATE)
        AND
        LEFT(Referral, 2) = 'RE'
) t
GROUP BY t.Referral
ORDER BY Total DESC
Comments