0

I have a table which contain value like this

+-------+-----------+------------+------------+-----------+
| User  | activity1 | activity2  | activity3  | activity4 |
+-------+-----------+------------+------------+-----------+
| user1 | A.C.E A1  | A....      | ABCDE ZZZ  | A.C..     |
| user2 | A.C..     | A.CD.      | ABCDE text | ABCD.     |
| user3 | ABC..     | A.C.E free | A....      | A....     |
+-------+-----------+------------+------------+-----------+

I want result something like

+-------+---+---+---+---+
| User  | A | B | C | D |
+-------+---+---+---+---+
| user1 | 4 | 1 | 3 | 1 |
| user2 | 4 | 2 | 4 | 3 |
| user3 | 4 | 1 | 2 | 0 |
+-------+---+---+---+---+

Result of A B C and D are summary of present of each text in columns activity1..4 for each row.

I try with SUM(CASE WHEN which I must do something wrong and it give me wrong count. I can not get count result more than 1.

Text after E would be free text what ever can be possible and don't care at the moment.

This data was original in csv format export from some other system which I can have to get it this way then import to mysql server.

I am not expert in sql. Could someone help to find solution for query.

user2749698
  • 3
  • 2

1 Answers1

0

If I understand correctly, you want to count the number of "A"s, "B"s and so on in each row.

The basic idea is something like:

select user,
       ((activity1 like '%A%') + (activity2 like '%A%') + (activity3 like '%A%') + (activity4 like '%A%')) as a,
       ((activity1 like '%B%') + (activity2 like '%B%') + (activity3 like '%B%') + (activity4 like '%B%')) as b,
       ((activity1 like '%C%') + (activity2 like '%C%') + (activity3 like '%C%') + (activity4 like '%C%')) as c,
       ((activity1 like '%D%') + (activity2 like '%D%') + (activity3 like '%D%') + (activity4 like '%D%')) as d
from t;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you Gordon Linoff. This help me getting success with my problem which I was try many way. One more explanation want. In this case when mysql process "activity1 like '%A%'" It will return as "1" or true? – user2749698 Feb 09 '16 at 04:20
  • maybe this can help you understand http://stackoverflow.com/questions/10852337/true-false-vs-0-1-in-mysql . Mysql treat `true/false` as `1/0` – Juan Carlos Oropeza Feb 09 '16 at 12:20
  • Thanks Juan to help open give me more knowledge – user2749698 Feb 10 '16 at 07:03