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.

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

CodePudding user response：

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

CodePudding user response：

You probably have the Person's (maybe Employee table) central table I am guessing, if yes, then you can inner join on that table using these 2 fields and then do a group on the ID/Name.

declare @booking table(BookingID int not null identity, BookingRep2 varchar(100), SalesAssist varchar(100));

insert into @booking(BookingRep2, SalesAssist)
    values ('Brenda', null)
        , ('Brenda', null)
        , ('Jim', 'Brenda')
        , ('Jim', null)

declare @person table(Person varchar(100));

insert into @person(Person)
    values ('Brenda')
            , ('Jim')
            , ('John')

select  p.Person, count(1) as TotalTimes
from    @booking as b
        inner join @person as p on b.BookingRep2 = p.Person
                                        or b.SalesAssist = p.Person
group by p.Person

-- Output:
--    Person  |  TotalTimes
--    --------|------------
--    Brenda  |  3
--    Jim     |  2