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
``````

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
``````