count value change in sqlite

Question

i have the following table:

    CREATE TABLE ta91 (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
    date INTEGER, 
    _520  INTEGER, 
    _530 INTEGER,
    _540 INTEGER,
    _550 INTEGER,
    _560 INTEGER,
    _570 INTEGER,
    _580 INTEGER,
    _590 INTEGER,
    _600 INTEGER,
    _610 INTEGER,
    _620 INTEGER,
    _630 INTEGER,
    _640 INTEGER,
    _650 INTEGER,
    _660 INTEGER,
    _670 INTEGER,
    _680 INTEGER,
    _690 INTEGER,
    _700 INTEGER,
    _710 INTEGER,
    _720 INTEGER,
    _730 INTEGER,
    _740 INTEGER,
    _750 INTEGER,
    _760 INTEGER,
    _770 INTEGER,
    _780 INTEGER,
    _790 INTEGER,
    _800 INTEGER,
    _810 INTEGER,
    _820 INTEGER,
    _830 INTEGER,
    _840 INTEGER,
    _850 INTEGER
)

with the values:

INSERT INTO "someTable" VALUES ("10","1355480306018","1","0","0","0","0","1","0","0","0","0","1","1","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0");
INSERT INTO "someTable" VALUES ("11","1355480307044","1","0","0","0","0","0","0","0","1","0","0","0","1","0","0","0","0","0","1","1","1","1","0","0","0","0","0","0","0","0","0","0","0","0");
INSERT INTO "someTable" VALUES ("12","1355480308027","1","0","0","0","0","0","0","0","1","0","0","0","1","0","0","0","0","0","1","1","1","1","0","0","0","0","0","0","0","0","0","0","0","0");
INSERT INTO "someTable" VALUES ("13","1355480309033","1","0","0","0","0","1","0","0","0","0","1","1","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0");
INSERT INTO "someTable" VALUES ("14","1355480310038","1","0","0","0","0","1","0","0","0","0","1","1","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0");
INSERT INTO "someTable" VALUES ("15","1355480311043","1","0","0","0","0","0","0","0","1","0","0","0","1","0","0","0","0","0","1","1","1","1","0","0","0","0","0","0","0","0","0","0","0","0");
INSERT INTO "someTable" VALUES ("16","1355480312043","1","0","0","0","0","0","0","0","1","0","0","0","1","0","0","0","0","0","1","1","1","1","0","0","0","0","0","0","0","0","0","0","0","0");
INSERT INTO "someTable" VALUES ("17","1355480313048","1","0","0","0","0","1","0","0","0","0","1","1","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0","0");

how can i count the number of times the value switches from 0/1 resp. 1/0 for each column directly in sql?

i am using c# with System.Data.SQLite.dll 1.0.82.0 to access the database

I think for illustrating your question, a table with two or three value columns would suffice. Also: Have you found out parts of a solution already? e.g. have you tried solving the problem for only one value column? Please describe whatever you have done so far in the question, as well. — O. R. Mapper, Jan 26 '13 at 14:07
I am wondering personally what it is the OP is truly trying to achieve it would really make sense if the OP would add more details explaining what he / she is trying to do sounds like some sort of Binary Switch based on the 1's and 0's but can't tell without having more information — MethodMan, Jan 26 '13 at 14:09
@MD.Unicorn i am looking for a performant sql statement to count the number of value changes e.g. for the first column it would be 0, for the sixth column it would be 4 and so on — user1320852, Jan 26 '13 at 14:14
@DJKRAZE you are right. imagine each of these columns as light on/off switches. i want to count how many times the switch has been switched on/off — user1320852, Jan 26 '13 at 14:18
i already found a familiar thread: http://stackoverflow.com/questions/8056527/sql-count-number-of-changes-in-an-ordered-list its just that sqlite doesnt support LEAD and LAG functions — user1320852, Jan 26 '13 at 14:20

score 1 · Accepted Answer · answered Jan 26 '13 at 14:17

Assuming

no portability requirements outside of sqlite;
consecutive id values;
the need to count (for each column) total number of times for both 0-to-1 and 1-to-0 switches;
table name being someTable, like in your INSERT statements;

You can use this:

SELECT SUM(a._520<>b._520) AS s_520,
       SUM(a._530<>b._530) AS s_530,
       SUM(a._540<>b._540) AS s_540,
       SUM(a._550<>b._550) AS s_550,
       SUM(a._560<>b._560) AS s_560,
       SUM(a._570<>b._570) AS s_570,
       SUM(a._580<>b._580) AS s_580,
       SUM(a._590<>b._590) AS s_590,
       SUM(a._600<>b._600) AS s_600,
       SUM(a._610<>b._610) AS s_610,
       SUM(a._620<>b._620) AS s_620,
       SUM(a._630<>b._630) AS s_630,
       SUM(a._640<>b._640) AS s_640,
       SUM(a._650<>b._650) AS s_650,
       SUM(a._660<>b._660) AS s_660,
       SUM(a._670<>b._670) AS s_670,
       SUM(a._680<>b._680) AS s_680,
       SUM(a._690<>b._690) AS s_690,
       SUM(a._700<>b._700) AS s_700,
       SUM(a._710<>b._710) AS s_710,
       SUM(a._720<>b._720) AS s_720,
       SUM(a._730<>b._730) AS s_730,
       SUM(a._740<>b._740) AS s_740,
       SUM(a._750<>b._750) AS s_750,
       SUM(a._760<>b._760) AS s_760,
       SUM(a._770<>b._770) AS s_770,
       SUM(a._780<>b._780) AS s_780,
       SUM(a._790<>b._790) AS s_790,
       SUM(a._800<>b._800) AS s_800,
       SUM(a._810<>b._810) AS s_810,
       SUM(a._820<>b._820) AS s_820,
       SUM(a._830<>b._830) AS s_830,
       SUM(a._840<>b._840) AS s_840,
       SUM(a._850<>b._850) AS s_850
FROM "someTable" a INNER JOIN "someTable" b
ON a.id = b.id-1;

Of course, I've generated this thing. So could you.

count value change in sqlite

1 Answers1