Create table that filters and counts grouped by column

[BTC]

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.

[Nicolas_Joseph]

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
  
  
• 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
  
  
• Query backlog (Query), same as previously, with a different configuration:
  
  
• total in backlog (Rollup), again, same logic to apply:
  
  

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):

Hope that’s helping!

[BTC]

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