SQL Calculations With Multi-Group Affiliations

I'm attempting to have a function or view that is able to calculate and roll up various counts while being able to search on a many to many affiliation.

Here is an example data set:

Invoice Table:
InvoiceID LocationID   StatusID
1         5            1
2         5            1
3         5            1
4         5            2
5         7            2
5         7            1
5         7            2

Group Table:
GroupID   GroupName
1         Group 1
2         Group 2

GroupToLocation Table:
GroupToLocationID GroupID  LocationID
1                 1        5
2                 2        5
3                 2        7

I have gotten to the point where I could sum up the various statuses per location and get this:

 LocationID     Status1   Status2
 5              3         1
 7              1         2

Location 5 has 3 Invoices with a status of 1, and 1 invoice with a status of 2 while Location 7 has 1 status 1 and 2 status 2

There are two groups, and Location 5 is in both, while Location 7 is only in the second. I need to be able to set it up where I can append a where statement like this:

select * from vw_GroupCounts
where GroupName = 'Group 2'

or

select Invoice, SUM(*) from vw_GroupCounts
where GroupName = 'Group 2'

And that result in only getting Location 7. Whenever I do this, as I have to use left joins or something along those lines, the counts are duplicating for each group the the Location is affiliated with. I know I could do something along the lines of a subquery and pass in the GroupName into that, but the system I am working with uses a dynamic query builder that appends WHERE statements based on user input.

I don't mind using view, or functions, or any number of functions inside of functions, but I hope there is a way to do what I'm looking for.

1 answer

  • answered 2018-03-13 21:21 bpfrenchak

    Since locations 5 and 7 are in Group 2, if you search for group 2 in the where clause after joining all the tables, then you would get all records in this case, this isn't duplication, just the way the data is. A different join wouldn't change this, only changing the data. Let me know if I am misunderstanding something though.

    Here is how you would join them to do that search.

    enter image description here

    Here it is with your first example of the location and status count.

    enter image description here