How do you count across rows here? A cell with a Date of Birth should be counted

Question

Primary Key	Child1DOB	Child2DOB	Child3DOB	Child4DOB
mother1	12.09.00	05.04.97	NULL	NULL
mother2	09.06.01	05.09.98	06.04.07	NULL
mother3	12.06.02	03.14.99	06.04.08	NULL
mother4	09.07.03	05.02.00	06.04.09	NULL
mother5	10.06.04	05.11.12	08.05.97	01.03.95

I basically need to count the amount of children each mother has. Thank you in advance.

Could you please tag your DBMS and add the expected result as second table? — Jonas Metzler, Dec 17 '22 at 18:39
It seems that's just a duplicate of https://stackoverflow.com/questions/18193365/count-of-non-null-columns-in-each-row. If not, please add more details to your question. — Jonas Metzler, Dec 17 '22 at 18:50

score 1 · Answer 1 · answered Dec 17 '22 at 18:44

You can add up 1 for each non-null field:

SELECT PrimaryKey, 0+(CASE WHEN Child1DOB IS NOT NULL THEN 1 ELSE 0 END)
                    +(CASE WHEN Child2DOB IS NOT NULL THEN 1 ELSE 0 END)
                    +(CASE WHEN Child3DOB IS NOT NULL THEN 1 ELSE 0 END)
                    +(CASE WHEN Child4DOB IS NOT NULL THEN 1 ELSE 0 END) AS numChildren
FROM tab

or if your NULL values are always found within the last columns, you can check for each column if there's a null or not, and state the number of children directly:

SELECT PrimaryKey, 
       CASE WHEN Child4DOB IS NOT NULL THEN 4
            WHEN Child3DOB IS NOT NULL THEN 3
            WHEN Child2DOB IS NOT NULL THEN 2
            ELSE 1 END AS numChildren
FROM tab

Both these two queries are likely to work on any DBMS that features the CASE statement.

How do you count across rows here? A cell with a Date of Birth should be counted

1 Answers1