SQL question - specific filtering

[backprop]

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.

[gidesa]

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]

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. from
3. (select collevel, case when collevel = 'Level3' then colcritical else '' end as critical
4.  from states)
5. 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]

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

[backprop]

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 statistic
from
(
  select level,

  case when
    (level <> 'Level1') and (level <> 'Level2')
    then critical
    else ''
  end as critical

from states)

group by level, critical;


or

Code: [Select]

select level, '' as critical, count(*) as count from states
where
  (level='Level1') or (level='Level2')
group by 1

union all

select level, critical, count(*) as count from states
where
  (level <> 'Level1') and (level <> 'Level2')
group by 1, 2

order 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

[backprop]

Let also say that I have list of these warning and weight of it. For instance:

Code: [Select]


level, weight

Level1 1
Level2 5
Level3 Green 1
Level3 Yellow 50
Level3 Red 100

Level4 Green 1
Level4 Yellow 500
Level4 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...

[Zvoni]

Interesting issue.

Do you have some concrete data-samples?

"This is what i have, and that's what i want"

[Zvoni]

Something like this?
https://www.db-fiddle.com/f/wi3dXVZTSsnKjXUaQVAocp/0

Note: SQLite