curious curious - 5 months ago 7
SQL Question

SQL Server 2012 : how to get count group by from multiple table joins

There's an invoice table, with the person who has created the invoice. A person can belong to multiple offices, only one main office per person but same person can have multiple roles per office.

declare @person table (personid int)
declare @office table (officeid int, officename varchar(10))
declare @personoffice table (personid int, officeid int, mainoffice bit, personrole varchar(10))
declare @invoice table (personid int)

insert into @person values (1), (2), (3), (4)
insert into @office values (1, 'office1'), (2, 'office2'), (3, 'office3'), (4, 'office4')
insert into @personoffice values (1, 1, 1, 'role1'), (1, 1, 1, 'role2'), (1, 2, 0, 'role1'), (1, 3, 0, 'rolex'), (2, 2, 1, 'role1'), (2, 2, 1, 'role2'), (2, 3, 0, 'rolex'), (3, 3, 1, 'role1'), (3, 4, 0, 'role2')
insert into @invoice values (1), (1), (1), (2), (2), (3), (3), (3), (3), (3)


So for this example we have 3 persons, they belong to multiple offices but only one main office each but some persons have multiple roles per office. They have each created multiple invoices.

I can get the number of invoices per person with:

select
i.personid,
count(*) InvoiceCountByPerson
from
@invoice i
inner join
@person p on p.personid = i.personid
group by
i.personid


which returns:

personid InvoiceCountByPerson
--------------------------------
1 3
2 2
3 5


I need to get number of invoices by main office name. Person1 whose main office is office1 created 3 invoices, Person2 whose main office is office2 created 2 invoices, and Person3 whose main office is office3 created 5 invoices so expected result:

officename InvoiceCountByOfficeName
------------------------------------
office1 3
office2 2
office3 5


This doesn't work:

select
o.officename,
count(*) InvoiceCountByOfficeName
from
@invoice i
inner join
@person p on p.personid = i.personid
inner join
@personoffice po on po.personid = p.personid AND po.mainoffice = 1
inner join
@office o on o.officeid = po.officeid
group by
o.officename


as it returns:

officename InvoiceCountByOfficeName
-------------------------------------
office1 6
office2 4
office3 5


As the same person has multiple mainoffice = 1 records with different roles, I need to have some sort of distinct on the @personoffice join. Millions of invoices too so need to take performance into consideration.

Answer

You are so close... All you had to do is use a derived table instead of using the @personoffice table directly:

select 
    o.officename,
    count(*) InvoiceCountByOfficeName
from 
    @invoice i
inner join 
    @person p on p.personid = i.personid
inner join 
    (
        select distinct personid, officeid
        from @personoffice
        where mainoffice = 1
    )
     po on po.personid = p.personid 
inner join 
    @office o on o.officeid = po.officeid
group by 
    o.officename

Results:

officename InvoiceCountByOfficeName
---------- ------------------------
office1    3
office2    2
office3    5