Counting 2 Columns as 1 in SQL

Question

I am attempting to select a count of rows for grouped by 2 columns. For instance, in the following table, I need to return 3 for Brenda, and 2 for Jim.

bookingid	bookingrep2	sales_assist
1	Brenda
2	Brenda
3	Jim	Brenda
4	Jim

If the persons name is in either bookingrep2 or sales_assist columns, they are counted. I was thinking this would be a union, but in this case the count is doubled.

query edited for clarity...

SELECT        bookingid, sales_assist AS Regional_Rep
FROM            bookings
UNION ALL
SELECT bookingid, bookingRep2 AS Regional_Rep
FROM            bookings

Answer 1

Option 1: unpivot

select   u.person
        ,count(*) as cnt
from     bookings as b unpivot (person for col in (bookingrep2, sales_assist)) as u
group by u.person

---

Option 2: cross apply + values

select   ca.person
        ,count(*) as cnt
from     bookings as b cross apply (values (bookingrep2), (sales_assist)) as ca (person)
where    ca.person is not null
group by ca.person

---

+--------+-----+
| person | cnt |
+--------+-----+
| Brenda |   3 |
| Jim    |   2 |
+--------+-----+

Fiddle