How to count the number of fields in a database column? (excluding NULLS, not counting them)-CodePudding

Read the part below the image before criticizing, thanks. I would like to count how many fields there are in the ID_Campionato vertical column. In all there are 13, so I would like to get the result of 13. The table is called ARCHIVIO_Squadre_Campionato, while the column ID_Campionato. I know that records and columns are the same, but I need them for a test, because the fields of the column are added separately later. If they don't align, some fields will have a NULL. So I would like to count the fields of the column excluding any NULLs from the count, to check that there are no problems.

IMPORTANT I know, now you can tell me that I can simply count the rows of the records, but I reply that the data in the vertical column ID_Campionato is inserted later, inserted separately from everything else. So there could be an error because, by inserting them separately at a later time, they may not be the same with the number of records already present in the preceding. If they don't align, some fields will have a NULL. So I would like to count the fields of the column excluding any NULLs from the count, to check that there are no problems. That's why I want to check: I need it for some sort of test.The column ID_Campionato I add it later, after I have already created the records. I add it separately, because it is a column copied from another table. So I asked this question because I would like to do a test, to see if the fields of the ID_Campionato column have all been inserted correctly (since they are all inserted at a later time).

UPDATE FOR HELP

    con = sqlite3.connect('database.db')
    cursor = con.cursor()


    #Inserts all the Serie A teams all over again
    sqlite_insert_query_SerieA = 'INSERT INTO ARCHIVIO_Squadre_Campionato (Nome_Squadra) VALUES (?);'
    cursor.executemany(sqlite_insert_query_SerieA, Values_SerieA)
    count_squadre_SerieA = cursor.rowcount
    con.commit()      

    #Copy and insert, in the Teams table, the ID of the relative championship
    cursor.executescript("""
        UPDATE ARCHIVIO_Squadre_Campionato
        
        SET ID_Campionato= (
            SELECT ID_Campionato
            FROM ARCHIVIO_Campionati
            WHERE Nome_Campionato = 'Serie A')
            
        WHERE ID_Campionato IS NULL;
        """)

CodePudding user response：

Consider the following exchange, done in SQLite3. Is this what you're after?

DROP TABLE one;
DROP TABLE two;

.mode box

CREATE TABLE one (
  ID_Sq integer not null primary key,
  Nome_Squadra char(255),
  ID_Campionato integer
);

CREATE TABLE two (
  ID_Sq integer not null primary key,
  ID_Campionato integer
);

INSERT INTO one (ID_Sq, Nome_Squadra) VALUES
    (2865, 'Bologna'),
    (2866, 'Torino'),
    (2867, 'Udinese'),
    (2868, 'Sampdoria'),
    (2869, 'Venizia'),
    (2870, 'Spezia'),
    (2871, 'Genoa'),
    (2872, 'Salernitana'),
    (2873, 'Califari'),
    (2874, 'Pisa'),
    (2875, 'Benevento'),
    (2876, 'Reggina'),
    (2877, 'Brescia');


INSERT INTO two (ID_Sq, ID_Campionato) VALUES
    (2865, 1),
    (2866, 1),
    (2867, 1),
    (2868, 1),
    (2869, 1),
    (2870, 1),
    (2873, 1),
    (2874, 2),
    (2875, 2),
    (2876, 2),
    (2877, 2);

SELECT * FROM one;
┌───────┬──────────────┬───────────────┐
│ ID_Sq │ Nome_Squadra │ ID_Campionato │
├───────┼──────────────┼───────────────┤
│ 2865  │ Bologna      │               │
│ 2866  │ Torino       │               │
│ 2867  │ Udinese      │               │
│ 2868  │ Sampdoria    │               │
│ 2869  │ Venizia      │               │
│ 2870  │ Spezia       │               │
│ 2871  │ Genoa        │               │
│ 2872  │ Salernitana  │               │
│ 2873  │ Califari     │               │
│ 2874  │ Pisa         │               │
│ 2875  │ Benevento    │               │
│ 2876  │ Reggina      │               │
│ 2877  │ Brescia      │               │
└───────┴──────────────┴───────────────┘
SELECT * FROM two;
┌───────┬───────────────┐
│ ID_Sq │ ID_Campionato │
├───────┼───────────────┤
│ 2865  │ 1             │
│ 2866  │ 1             │
│ 2867  │ 1             │
│ 2868  │ 1             │
│ 2869  │ 1             │
│ 2870  │ 1             │
│ 2873  │ 1             │
│ 2874  │ 2             │
│ 2875  │ 2             │
│ 2876  │ 2             │
│ 2877  │ 2             │
└───────┴───────────────┘
UPDATE one SET ID_Campionato=two.ID_Campionato FROM two WHERE one.ID_Sq=two.ID_Sq;
SELECT * FROM one;
┌───────┬──────────────┬───────────────┐
│ ID_Sq │ Nome_Squadra │ ID_Campionato │
├───────┼──────────────┼───────────────┤
│ 2865  │ Bologna      │ 1             │
│ 2866  │ Torino       │ 1             │
│ 2867  │ Udinese      │ 1             │
│ 2868  │ Sampdoria    │ 1             │
│ 2869  │ Venizia      │ 1             │
│ 2870  │ Spezia       │ 1             │
│ 2871  │ Genoa        │               │
│ 2872  │ Salernitana  │               │
│ 2873  │ Califari     │ 1             │
│ 2874  │ Pisa         │ 2             │
│ 2875  │ Benevento    │ 2             │
│ 2876  │ Reggina      │ 2             │
│ 2877  │ Brescia      │ 2             │
└───────┴──────────────┴───────────────┘
SELECT COUNT(*) FROM one WHERE ID_Campionato IS NOT NULL;
┌──────────┐
│ COUNT(*) │
├──────────┤
│ 11       │
└──────────┘
sqlite>

Which, of course, becomes:

cursor.execute("SELECT COUNT(*) FROM one WHERE ID_Campionato IS NOT NULL;")
print(cursor.fetchOne()[0])