SELECT COUNT of multiple column under the same table

Posted on

Question :

Im blowing my brain on this one. I think I am making this harder than I need to. Im trying to run a mySQL query to SELECT a COUNT of members which have any values in any column. my example table is as such:

enter image description here

So far, I have got

SELECT COUNT(members)
FROM table_1
WHERE '1' IN column_1,
OR column_2,
OR column_3,
OR column_4

Please help, I am returning the craziest results

My results should look like

Member———-3

Answer :

SELECT COUNT(COALESCE(column_1, column_2, column_3, column_4))
FROM table_1

If all columns are NULL, COALESCE() will also return NULL and the row won’t be counted.

The use of COALESCE() will do what you ask for very nicely, but I would also question if you might want to reconsider your schema. It looks like what you’re doing is tracking individuals’ membership in various groups (column_1 … column_X).

You would probably fare far better to simply make a membership table with two columns: member_id and group_id. You could then more easily add/remove groups from the groups table and counting memberships would have been as simple as a COUNT() on rows matching the member_id in the membership table. This would also set you up better for varying types of members (additional field on membership). Just a thought on a better way to plan for future needs here.

Leave a Reply

Your email address will not be published.