Forum Discussion
Table of Data: Is Countifs my best solution?
Skip the filter_array argument with a comma or stop the formula at:
=GROUPBY(DemoTbl[Model],DemoTbl[PN],ARRAYTOTEXT,,0)
have several hundreds of thousands of rows, so the countfs are really slow with calculating.
If too many rows cause slow,you can import all datas to sqlite database.
Then run sql:select model,count(pn),group_concat(pn) from sheet1 group by model
https://www.sqlitetutorial.net/sqlite-group_concat/
what models have PN1 and PN3?
select * from Sheet1 limit 20;
create temp table aa as
select regexp2('^(.*):(.*)$',f01,1) model,regexp2('^(.*):(.*)$',f01,2) pn from Sheet1;
//select * from aa;
select model,group_concat(pn) from aa group by model;
create temp table bb as
select * from aa where regexp('PN1|3',pn);
select *,group_concat(pn) from bb group by model having(count(distinct(pn))>1);
select * from Sheet1 limit 20;
create temp table aa as
select regexp2('^(.*):\s*(.*)$',f01,1) model,regexp2('^(.*):\s*(\S*)$',f01,2) pn from Sheet1;
//select *,instr('PN1,PN3',pn) ,instr('PN',pn) from aa;select *,group_concat(pn) from aa where instr('PN1,PN3',pn)>0 group by model having(count(distinct(pn))>1);
Thank you. What does the 'limit 20' do? I have over 30 part numbers and close to 200k models...
select * from Sheet1 limit 20;
This is only for show raw data sturcture.
After you split one column to 2 column model and pn,you just need below sql to show what models have PN1 and PN3.
select *,group_concat(pn) from aa where instr('PN1,PN3',pn)>0 group by model having(count(distinct(pn))>1);
This SQL query is designed to retrieve records from a table, specifically focusing on grouping and filtering rows based on certain conditions. Let's break down the query step by step:
### Query Breakdown:
```sql
SELECT *, GROUP_CONCAT(pn)
FROM aa
WHERE INSTR('PN1,PN3', pn) > 0
GROUP BY model
HAVING COUNT(DISTINCT pn) > 1;
```### 1. **`SELECT *, GROUP_CONCAT(pn)`**
- `SELECT *`: This selects all columns from the `aa` table.
- `GROUP_CONCAT(pn)`: This is an aggregate function that concatenates the values in the `pn` column (the primary key or identifier you're interested in) for each group formed by the `GROUP BY` clause. The result is a comma-separated string of all `pn` values within each group.### 2. **`FROM aa`**
- Specifies the table `aa` from which to retrieve the data.### 3. **`WHERE INSTR('PN1,PN3', pn) > 0`**
- `INSTR('PN1,PN3', pn)` is a string function that checks if the value of `pn` is present within the string `'PN1,PN3'`.
- The `INSTR()` function returns the position of the first occurrence of `pn` within `'PN1,PN3'`. If it finds the `pn` in the list, the function returns a positive number (the position where it was found). If it doesn't find it, it returns `0`.
- The condition `INSTR('PN1,PN3', pn) > 0` ensures that only rows with `pn` values equal to `'PN1'` or `'PN3'` will be included in the result. So, the query filters out any rows where `pn` is not `'PN1'` or `'PN3'`.### 4. **`GROUP BY model`**
- This clause groups the rows by the `model` column. After grouping, the aggregate functions (like `GROUP_CONCAT(pn)`) will be applied to each group.### 5. **`HAVING COUNT(DISTINCT pn) > 1`**
- `HAVING` is used to filter groups after they have been formed by the `GROUP BY` clause.
- `COUNT(DISTINCT pn) > 1` checks that there is more than one unique `pn` in each group. This means that, for each group, there must be at least two distinct `pn` values (either `'PN1'` and `'PN3'` in this case) for the group to be included in the result.### Putting It All Together:
- **Step 1**: The query selects all rows from the table `aa`, but only keeps those where `pn` is either `'PN1'` or `'PN3'`.
- **Step 2**: It groups the selected rows by the `model` column.
- **Step 3**: For each group, it checks that there are at least two distinct `pn` values (either `'PN1'` and `'PN3'`), and only those groups that satisfy this condition are included in the result.
- **Step 4**: The query includes all columns of the table (due to `SELECT *`) and a concatenated list of the `pn` values (using `GROUP_CONCAT(pn)`) for each group.### Example:
Suppose your table `aa` has the following data:
| model | pn |
|-------|-----|
| A | PN1 |
| A | PN3 |
| A | PN1 |
| B | PN2 |
| B | PN1 |
| C | PN3 |
| C | PN3 |- After applying `WHERE INSTR('PN1,PN3', pn) > 0`, the data will be filtered to:
| model | pn |
|-------|-----|
| A | PN1 |
| A | PN3 |
| A | PN1 |
| C | PN3 |
| C | PN3 |- The `GROUP BY model` step will group the rows by `model`, resulting in two groups:
- Group 1: `model = A`
- Group 2: `model = C`- The `HAVING COUNT(DISTINCT pn) > 1` condition will filter out the group where there is only one unique `pn` value. Since `model C` has only `PN3` values, it will be excluded.
- Finally, `GROUP_CONCAT(pn)` will concatenate the `pn` values for the `model A` group.
The result would look like:
| model | pn | GROUP_CONCAT(pn) |
|-------|------|------------------|
| A | PN1 | PN1,PN3,PN1 |In this example:
- Only the `model A` group remains, because it contains both `PN1` and `PN3`.
- The `pn` values are concatenated into a single string: `'PN1,PN3,PN1'`.### Summary:
This SQL query selects groups of rows from the `aa` table, where the `pn` values are either `'PN1'` or `'PN3'`. It then groups these rows by the `model` column and filters out groups where there is not at least one distinct value of `pn` from the set `('PN1', 'PN3')`. For the remaining groups, it concatenates the `pn` values into a comma-separated string and returns the result.