# How to count occurrences matching two criteria in another table

**URL:** <https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593>\
**Category:** Ask for Help\
**Created:** [September 28, 2023, 6:40am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593 "2023-09-28T06:40:48Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![dvf061](https://avatars.discourse-cdn.com/v4/letter/d/0ea827/32.png) [@dvf061](https://community.glideapps.com/u/dvf061)\
**Post date:** [September 28, 2023, 6:40am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/1 "2023-09-28T06:40:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [September 28, 2023, 9:32am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/2 "2023-09-28T09:32:16Z")

</div>

> [@dvf061](#):
>
> 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.

---

<div class="post-metadata">

**Author:** ![dvf061](https://avatars.discourse-cdn.com/v4/letter/d/0ea827/32.png) [@dvf061](https://community.glideapps.com/u/dvf061)\
**Post date:** [September 28, 2023, 9:57am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/3 "2023-09-28T09:57:08Z")

</div>

@Darren_Murphy here is my screenshot of my tables :

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/e/b/eb32a653095967e9d4ab16d1a891ad9cf59782e0.jpeg)

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

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/4/54d67fd3dac240e9e4952fe8c6ff3f556e7fe6dc.jpeg)

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [September 28, 2023, 10:07am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/4 "2023-09-28T10:07:50Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![dvf061](https://avatars.discourse-cdn.com/v4/letter/d/0ea827/32.png) [@dvf061](https://community.glideapps.com/u/dvf061)\
**Post date:** [September 28, 2023, 10:35am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/5 "2023-09-28T10:35:41Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [September 28, 2023, 10:38am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/6 "2023-09-28T10:38:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dvf061](https://avatars.discourse-cdn.com/v4/letter/d/0ea827/32.png) [@dvf061](https://community.glideapps.com/u/dvf061)\
**Post date:** [September 28, 2023, 10:43am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/7 "2023-09-28T10:43:41Z")

</div>

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

```auto
CREATE VIEW emp_ben_view AS
SELECT 
    e.id, 
    e.empCode, 
    COUNT(b.empCode) AS empCode_matches, 
    SUM(b.hubspot) AS hubspot_ones
FROM 
    enw_emp e
LEFT JOIN 
    enw_ben b ON e.empCode = b.empCode
GROUP BY 
    e.id, 
    e.empCode;

```

---

<div class="post-metadata">

**Author:** ![dvf061](https://avatars.discourse-cdn.com/v4/letter/d/0ea827/32.png) [@dvf061](https://community.glideapps.com/u/dvf061)\
**Post date:** [September 28, 2023, 10:52am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/8 "2023-09-28T10:52:26Z")

</div>

that works, 100%. thank you

---

<div class="post-metadata">

**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\
**Post date:** [October 5, 2023, 10:52am UTC](https://community.glideapps.com/t/how-to-count-occurrences-matching-two-criteria-in-another-table/66593/9 "2023-10-05T10:52:54Z")

</div>

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