CODEWITHSUNDEEP
pythonsqlsqlite
codeKiller
codeKiller

Reputation: 5739

Complex query in SQL

I have a quite complex query (at least for my beginner level), so I hope anybody can help me with it.

Here it is an example of my table:

Device nr    type   state   I1     I2
    1          A     on     20     10
    1          A     off    20     10
    1          B     on     19     8
    1          B     off    20     10
    1          C     on     21     12
    1          C     off    20     10
    2          A     on     21     9
    2          A     off    20     10
    2          B     on     18     7
    2          B     off    20     10
    2          C     on     20     9
    2          C     off    20     10
    3          A     on     17     11
    3          A     off    20     10
    3          B     on     20     11
    3          B     off    20     10
    3          C     on     19     10
    3          C     off    20     10
    1          A     on     20     10
    1          A     off    20     10
    1          B     on     19     8
    1          B     off    20     10
    1          C     on     21     12
    1          C     off    20     10
    2          A     on     21     9
    2          A     off    20     10
    2          B     on     18     7
    2          B     off    20     10
    2          C     on     20     9
    2          C     off    20     10
    3          A     on     17     11
    3          A     off    20     10
    3          B     on     20     11
    3          B     off    20     10
    3          C     on     19     10
    3          C     off    20     10

My question is how could I group and count the devices that are considered ok.

Device is ok when all types within the device (A,B and C) are ok.

And type A or B or C is ok when its "on" state has I1>19 and I2<11.

With my limited SQL experience I know how to set conditions like:

select (*) from myTalbe
where (I1>19 and I2<11)

But how can I make it count when A and B and C are ok and all have same Device nr?

An example of a desired output would be something like:

Device nr    OKcount
   1            15
   2            9
   3            22

So, for example, this ouput would say that there are 15 devices with number 1 that have A and B and C ok within the device

Upvotes: 1

Views: 100

Answers (2)

konart
konart

Reputation: 1834

select "Device nr", count(*) as OKcount FROM (
    select DISTINCT "Device nr", type from Table1
    where (I1>19 and I2<11) and state="on"
)
group by "Device nr"
having OKcount = 3

Sqlfiddle: http://sqlfiddle.com/#!7/32d81/4

Upvotes: 1

Cl&#233;ment S&#233;veillac
Cl&#233;ment S&#233;veillac

Reputation: 58

To finalize the cool approach of konart's answer:

select "Device nr" from (select "Device nr", count(*) as OKcount from Table1
where (I1>19 and I2<11) and state="on"
group by "Device nr") where OKcount = 3

Upvotes: 1

Related Questions