SQLite/SQLAlchemy: Check values are either NULL or the same for several columns-CodePudding

I would like to check for several columns that: For each column the value is either NULL or the same for all records.

For example, the condition holds for the following table

 ------ ------ ------ ------ 
| Col1 | Col2 | Col3 | Col4 |
 ------ ------ ------ ------ 
| A    | B    | NULL | NULL |
| NULL | B    | C    | NULL |
| A    | B    | C    | NULL |
 ------ ------ ------ ------

How can I do that, preferably with one query?

EDIT:
An alternative question would be: How can I sum the distinct values of each of the selected columns

CodePudding user response：

You can check if the distinct number of values in each individual column is less than or equal to 1:

SELECT COUNT(DISTINCT Col1) <= 1 ok1,
       COUNT(DISTINCT Col2) <= 1 ok2,
       COUNT(DISTINCT Col3) <= 1 ok3,
       COUNT(DISTINCT Col4) <= 1 ok4
FROM tablename;

Or, you can get a result for the the whole table:

SELECT MAX(
         COUNT(DISTINCT Col1),
         COUNT(DISTINCT Col2),
         COUNT(DISTINCT Col3),
         COUNT(DISTINCT Col4)
       ) <= 1 ok  
FROM tablename;

See the demo.