# Create table that filters and counts grouped by column

**URL:** <https://community.glideapps.com/t/create-table-that-filters-and-counts-grouped-by-column/81755>\
**Category:** Ask for Help\
**Created:** [May 6, 2025, 11:18pm UTC](https://community.glideapps.com/t/create-table-that-filters-and-counts-grouped-by-column/81755 "2025-05-06T23:18:34Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![BTC](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@BTC](https://community.glideapps.com/u/BTC)\
**Post date:** [May 6, 2025, 11:18pm UTC](https://community.glideapps.com/t/create-table-that-filters-and-counts-grouped-by-column/81755/1 "2025-05-06T23:18:34Z")

</div>

I have a table imported from airtable that includes deviceID, device type, client, inBacklog (boolean).

I want to make a table that counts the number of deviceID, where the client is not (for example) “retired” or “inhouse”, grouped by device type. Then a second column that counts just deviceID that are in backlog.

example:

pulled from airtable:  
deviceID, device type, client, inBacklog:  
Device1, typeA, retired, no  
Device2, typeA, client1, no  
Device3, typeA, client2, yes  
Device4, typeB, client3, no  
Device5, typeB, in house, no  
Device6, typeC, client4, no  
Device7, typeC, client4, no  
Device8, typeC, client5, yes

Select count(\*),  
where client != “retired” and client != “in house”  
group by “device type”

Also:  
select count(\*)  
where inBacklog = yes  
group by “device type”

I’m Looking for a table that looks like (explanations in parenthesis not to be included):  
Device type, total in field, total in backlog  
**typeA, 2** (device2+device3, but not device1) **, 1** (device3)  
**typeB, 4** (device4, but not device5) **, 0** (no devices in backlog)  
**typeC, 3** (device6+device7+device8) **, 1** (device8)

I am obviously coming from a SQL background which I think maybe tripping me up. Thank you for any help you can provide.

---

<div class="post-metadata">

**Author:** ![Nicolas\_Joseph](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nicolas_joseph/32/76105_2.png) [@Nicolas\_Joseph](https://community.glideapps.com/u/Nicolas_Joseph)\
**Post date:** [May 6, 2025, 11:52pm UTC](https://community.glideapps.com/t/create-table-that-filters-and-counts-grouped-by-column/81755/2 "2025-05-06T23:52:54Z")

</div>

Hello @BTC 🙂

You can create a new `Types` table and add the following columns:

- `Device type` (Text) to store the values (typeA, typeB, typeC…)
- `Query in field` (Query) to bring rows from your `Devices` table according to your filters  
 ![Query in field](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/9/b/9bf44821175303c17a616c6cfadb6b32259957d3.png)
- `total in field` (Rollup) : it’s the way to aggregate values in Glide (and others no code tools). You’ll use the previous `Query in field` to (distinct) count values from _deviceID_  
 ![total in field](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/0/2/029fc98fde2c94d13c8a8568760387409d79757d.png)
- `Query backlog` (Query), same as previously, with a different configuration:  
 ![Query backlog](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/d/4/d4d3bd097923aec357ba962424f76f748868fc70.png)
- `total in backlog` (Rollup), again, same logic to apply:  
 ![total in backlog](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/8/c8adb65f4a9dda3908db9e36d8a7a3056377e9b1.png)

  

Because you mentioned your SQL background, you have two ways to create a _link_ between data in Glide: **Relation** and **Query** columns. The Query allows you to add filters (the WHERE clause in SQL) while Relation is faster if you don’t any.  
The **Rollup** colmun is the one you choose to apply aggregations (Count in this case but also Sum or Average if the column you target contains Numbers instead of Texts).  
And to give you a quick overview, you’ll be able to use others **Computed** columns to get other informations such as a list of the names of Devices from the Query (for example):

 ![List of Devices in field](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/1/5/15c657265dbe97acdd15a87c298b5d2c51e523cd.png)

  

Hope that’s helping!

---

<div class="post-metadata">

**Author:** ![BTC](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@BTC](https://community.glideapps.com/u/BTC)\
**Post date:** [May 8, 2025, 5:04pm UTC](https://community.glideapps.com/t/create-table-that-filters-and-counts-grouped-by-column/81755/3 "2025-05-08T17:04:11Z")

</div>

Thank you! Thank makes the query function make a little more sense. I will try to implement this!