### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: SQL question - specific filtering  (Read 826 times)

#### backprop

• Jr. Member
• Posts: 63
##### SQL question - specific filtering
« on: July 17, 2024, 01:48:08 pm »
I'm not fluent in SQL, thus some uncommon question.

I have table as this, all strings:
Code: Pascal  [Select][+][-]
1. Datetime        Level   Critical
2.         Level1  Red
3.         Level1  Red
4.         Level1  Yellow
5.         Level2  Green
6.         Level2  Red
7.         Level3  Green
8.         Level3  Yellow
9.         Level3  Green
10. .
11.

Thus are list of various warnings and critical states and during day should be created statistic as follows:
Code: Pascal  [Select][+][-]
1.         Level1  3
2.         Level2  3
3.         Level3  Green 2
4.         Level3  Yellow 1
5.

In short level 1 and 2 are not so critical warnings and should be just listed total number of it, but for all others should be also listed statistic of all states.

This can be done in one query or must be created new table from query, then filter each step?

That may be done in SQLite, for instance.

If not possible in one query, AFAIK, CREATE TABLE result AS .... is allowed in SQLite.
« Last Edit: July 17, 2024, 01:56:48 pm by backprop »

#### gidesa

• Jr. Member
• Posts: 97
##### Re: SQL question - specific filtering
« Reply #1 on: July 17, 2024, 02:07:46 pm »
Use 3 distinct query and do Union on these.
Of course they must have the same output columns. So for level 1 and 2  third col. will be a constant, for example "All states".

#### dseligo

• Hero Member
• Posts: 1300
##### Re: SQL question - specific filtering
« Reply #2 on: July 17, 2024, 02:31:05 pm »
You could also do it with subquery.

If you create table and data like this:
Code: MySQL  [Select][+][-]
1. create table states (coldatetime datetime, collevel text, colcritical text);
2. insert into states (collevel, colcritical) values ('Level1', 'Red');
3. insert into states (collevel, colcritical) values ('Level1', 'Red');
4. insert into states (collevel, colcritical) values ('Level1', 'Yellow');
5. insert into states (collevel, colcritical) values ('Level2', 'Green');
6. insert into states (collevel, colcritical) values ('Level2', 'Red');
7. insert into states (collevel, colcritical) values ('Level3', 'Green');
8. insert into states (collevel, colcritical) values ('Level3', 'Yellow');
9. insert into states (collevel, colcritical) values ('Level3', 'Green');

Then this query:
Code: MySQL  [Select][+][-]
1. select collevel, critical, count(*) as statistic
2. (select collevel, case when collevel = 'Level3' then colcritical else '' end as critical
3.  from states)
4. group by collevel, critical

Will produce:
Code: Text  [Select][+][-]
1. collevel  critical  statistic
2. Level1              3
3. Level2              2
4. Level3    Green     2
5. Level3    Yellow    1

#### Zvoni

• Hero Member
• Posts: 2497
##### Re: SQL question - specific filtering
« Reply #3 on: July 17, 2024, 04:24:18 pm »
Use 3 distinct query and do Union on these.
Of course they must have the same output columns. So for level 1 and 2  third col. will be a constant, for example "All states".
Not 3!
2 are enough

Untested
Code: SQL  [Select][+][-]
1. SELECT level, critical, COUNT(*) FROM MyTable WHERE level>='Level 3' GROUP BY level, critical
2. UNION ALL
3. SELECT level, '' AS Dummy, COUNT(*) FROM MyTable WHERE level<'Level 3' GROUP BY level, Dummy
4. ORDER BY 1
Note: The second Select doesn't work in all DBMS, because this is a GROUP BY SomeAlias
MySQL and Postgres can do it, others don't

Everything said: What's the Target DBMS? MySQL? SQLite?

and upfront: The way i would do it is definitely different (Keyword: CTE's pre-building the filter-criteria, so in the end it boils down to a simple JOIN)
though it's very close to dseligo's Solution
« Last Edit: July 17, 2024, 04:32:16 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

#### backprop

• Jr. Member
• Posts: 63
##### Re: SQL question - specific filtering
« Reply #4 on: July 17, 2024, 05:28:54 pm »
Code: [Select]
`drop table if exists states;create table states (  datetime datetime,  level text,  critical text);insert into states (level, critical) values ('Level1', 'Red');insert into states (level, critical) values ('Level1', 'Red');insert into states (level, critical) values ('Level1', 'Yellow');insert into states (level, critical) values ('Level2', 'Green');insert into states (level, critical) values ('Level2', 'Red');insert into states (level, critical) values ('Level3', 'Green');insert into states (level, critical) values ('Level3', 'Yellow');insert into states (level, critical) values ('Level3', 'Green');create index if not exists idx_level on states (level);create index if not exists idx_critical on states (critical);`
Code: [Select]
`select level, critical, count(*) as statisticfrom(  select level,  case when    (level <> 'Level1') and (level <> 'Level2')    then critical    else ''  end as criticalfrom states)group by level, critical;`
or

Code: [Select]
`select level, '' as critical, count(*) as count from stateswhere  (level='Level1') or (level='Level2')group by 1union allselect level, critical, count(*) as count from stateswhere  (level <> 'Level1') and (level <> 'Level2')group by 1, 2order by 1,2;`
Level 1,2,etc are just aliases for proper strings, thus it is not possible to use operators as "<" or ">" on strings here. Both fields have indices.

Thanks to both of you.

Furthermore, what would be faster? I believe nested select is always slower, but all depends on internal engine optimization
« Last Edit: July 17, 2024, 05:43:21 pm by backprop »

#### backprop

• Jr. Member
• Posts: 63
##### Re: SQL question - specific filtering
« Reply #5 on: July 17, 2024, 06:07:26 pm »
Let also say that I have list of these warning and weight of it. For instance:

Code: [Select]
`level, weight Level1 1Level2 5Level3 Green 1 Level3 Yellow 50Level3 Red 100Level4 Green 1 Level4 Yellow 500Level4 Red 1000...`
And that I also want to have weight value which could be multiplied with count as well to show more clearly the risk. Well, then could be used JOIN, but also is possible that raw data contain new string, not added yet. IDs could be added as well, but all that making life more complicated...
« Last Edit: July 17, 2024, 06:10:07 pm by backprop »

#### Zvoni

• Hero Member
• Posts: 2497
##### Re: SQL question - specific filtering
« Reply #6 on: July 18, 2024, 08:13:33 am »
Interesting issue.

Do you have some concrete data-samples?

"This is what i have, and that's what i want"
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

#### Zvoni

• Hero Member
• Posts: 2497
##### Re: SQL question - specific filtering
« Reply #7 on: July 23, 2024, 01:06:21 pm »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad