How to count occurrences matching two criteria in another table

Hi there,

I have two tables: BEN and EMP. Inside EMP there is a column called empCode, which is also present in BEN. In my EMP table, I setup a multiple relation that matches the empCode with the same column in my BEN table, and also a rollup column that does a count via the relation column. With that, I can count how many times the empCode matches rows inside BEN empCode column.

Now, I would like to create another count column, but this time I have a text column called “spot” inside the BEN table that contains either a 0 or 1. I would like to reflect how many times it was 1 while also matching the empCode as well. So for example, let’s say I have 4 items counted already but only 2 of these items in BEN have 1, then the count in this new column within EMP should be 2.

I’ve read this about 5 times, but I’m not quite getting it. (probably my fault, because I’m not focussing very well).

Would you mind adding a screenshot of the two tables involved? It will make it so much easier to visualise.

@Darren_Murphy here is my screenshot of my tables :

and this is the expected result I am trying to achieve:

Okay, I think I get it…

If I’m understanding correctly, what you should be able to do is add a rollup column to your BEN table, and configure it to take the Max->hubspot via the benMatch relation column.

Does that give you the result you are looking for?

@Darren_Murphy Essentially yes. Those with hubspot status 1 are completed, so they should be counted. But, it should count only where the empCode matches. In essence that new column should count the number of completed items: those that have hubspot status 1.

For instance, let’s say I have those two rows in EMP table with their respective empCodes. If I would have 20 items in BEN that have hubspot status 1 for WXC… and 25 items that have hubspot status 1 for RH1, then the EMP column completed should be 20 for row with WXC and 25 for row with RH1, regardless of how many of them in BEN have hubspot status 0.

Okay, so instead of doing a rollup->max, do a rollup->sum.

That should do it. Just make sure you are targeting the relation column directly, and not via the EMP table.


@Darren_Murphy will try that and get back to you, thank you. Here is the MySQL code to illustrate this:

CREATE VIEW emp_ben_view AS
    COUNT(b.empCode) AS empCode_matches, 
    SUM(b.hubspot) AS hubspot_ones
    enw_emp e
    enw_ben b ON e.empCode = b.empCode

that works, 100%. thank you

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.