Using count on three different columns with where clause

Question

i am trying to count sub_tag_1,sub_tag_2,sub_tag_3 columns only when where condition is true and in the last i want sum of all three columns as a result.

here is my virtual table :

+---+-----------+-----------+-----------+
| id| sub_tag_1 | sub_tag_2 | sub_tag_3 |
+---+-----------+-----------+-----------+
| 1 |   php     |           |           |
| 2 |   mysql   |     php   |           |
| 3 |   java    |           |     php   |
+---+-----------+---------+-------------+

i already try so many times but i failed every time and here my sql query:

1) SELECT count(`sub_tag_1`) AND count(`sub_tag_2`) AND count(`sub_tag_3`) 
FROM posts where `sub_tag_1` 
LIKE "php" and `sub_tag_2` LIKE "php" and `sub_tag_3` LIKE "php";

above query executed successfully but it didn't match any thing in database and it return 0

2) SELECT count(`sub_tag_1`) AND count(`sub_tag_2`) AND count(`sub_tag_3`) 
FROM posts where `sub_tag_1` = "php" and `sub_tag_2` = "php" 
and `sub_tag_3` = "php" 
and sum(count(`sub_tag_1`)+count(`sub_tag_2`)+count(`sub_tag_3`));

above query give me error: Invalid use of group function

Please advice and explain step by step how can i do this..

Mureinik · Accepted Answer · 2014-02-01 09:20:58Z

1

Putting a confition in the WHERE clause will give you all the rows that have php in all three columns. Since there are none, you'll get the result 0.

What you're trying to do is retreive all the rows and only count the appropriate values. This can be done by using a CASE clause for each column, in the following fashion:

SELECT SUM(CASE sub_tag_1 WHEN 'php' THEN 1 ELSE 0 END) AS subg_tag_1_count, 
       SUM(CASE sub_tag_2 WHEN 'php' THEN 1 ELSE 0 END) AS subg_tag_2_count, 
       SUM(CASE sub_tag_3 WHEN 'php' THEN 1 ELSE 0 END) AS subg_tag_3_count
FROM   my_table
WHERE  'php' IN (sub_tag_1, sub_tag_2, sub_tag_3)

answered Feb 1, 2014 at 9:20

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using count on three different columns with where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related