MySQL: how to calculate from columns separately?

Question

Usually I feel pretty confident about SQL queries, however I have a scratching head. I feel that this should be a quick fix, but I just don't see it.

I am trying to do a count on several values ​​in one table in one query.

Ignore "0000000000000000" just by representing an empty byte array.

Is there an easy way to combine these queries?

SELECT COUNT(ssn)
FROM patients
WHERE ssn="0000000000000000";

SELECT COUNT(firstname)
FROM patients
WHERE firstname="0000000000000000"

SELECT COUNT(lastname)
FROM patients
WHERE lastname="0000000000000000"

etc...

Answer 1

You can do something like this -

SELECT COUNT(ssn) AS patient_count, 'ssn' AS count_type
FROM patients
WHERE ssn="0000000000000000";
UNION
SELECT COUNT(firstname) AS patient_count, 'firstname' AS count_type
FROM patients
WHERE firstname="0000000000000000"
UNION
SELECT COUNT(lastname) AS patient_count, 'lastname' AS count_type
FROM patients
WHERE lastname="0000000000000000"

Answer 2

SELECT SUM(CASE WHEN ssn = '0000000000000000' THEN 1 ELSE 0 END) AS ssn_count,
       SUM(CASE WHEN firstname = '0000000000000000' THEN 1 ELSE 0 END) AS first_count,
       SUM(CASE WHEN lastname = '0000000000000000' THEN 1 ELSE 0 END) AS last_count
    FROM patients
    WHERE ssn = '0000000000000000'
        OR firstname = '0000000000000000'
        OR lastname = '0000000000000000'

Answer 3

UNION

SELECT COUNT(ssn)
FROM patients
WHERE ssn="0000000000000000";
UNION
SELECT COUNT(firstname)
FROM patients
WHERE firstname="0000000000000000"
UNION    
SELECT COUNT(lastname)
FROM patients
WHERE lastname="0000000000000000"

Answer 4

, ?

SELECT *
FROM
(SELECT COUNT(ssn) AS ssn_count
 FROM patients
 WHERE ssn="0000000000000000") AS ssn
CROSS JOIN
(SELECT COUNT(firstname) AS firstname_count
 FROM patients
 WHERE firstname="0000000000000000") AS firstname
CROSS JOIN
(SELECT COUNT(lastname) AS lastname_count
 FROM patients
 WHERE lastname="0000000000000000") AS lastname