CODEWITHSUNDEEP
mysqlsql
Benedikt W
Benedikt W

Reputation: 183

COUNT several column values within one row

I am currently trying to count several column values in MySQL. My table looks as follows:

+------------+--------------+--------------+
|    date    | menu_person1 | menu_person2 |
+------------+--------------+--------------+
| 2020-04-29 | Menu 1       | Menu 1       |
+------------+--------------+--------------+
...

I would now like to identify the amount of "Menu 1" for a specific date. So in this case, the result of the code should be "2" for the selected date.

So far, I used the following code:

SELECT COUNT(*) AS menu_1_count FROM my_table WHERE date = "2020-04-29" AND (menu_person1 = "Menu 1" OR menu_person2 = "Menu 1");

However, this would only display 1 since both values are within the same row. Is there a way to count each column?

Upvotes: 1

Views: 57

Answers (2)

Dmitriy Pavlichenko
Dmitriy Pavlichenko

Reputation: 11

SELECT SUM(IF(menu_person1 = 'Menu 1', 1, 0) + IF(menu_person2 = 'Menu 1', 1, 0))
AS menu_1_count
FROM my_table
WHERE date = '2020-04-29';

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Perhaps the most efficient method is:

SELECT SUM(menu_person1 = 'Menu 1') + SUM(menu_person2 = 'Menu 1') AS menu_1_count
FROM my_table
WHERE date = '2020-04-29' ;

That said, your real problem is the data model. It is suspicious whenever you are multiple columns storing the same data. You should really have a table with one row per date and "menu person" -- whatever that refers to.

Upvotes: 2

Related Questions