SQL Question

Hi,

I want to count a string in different fields in one row of a table.

Table

id, field_1,field2, field3 ..... field_63,field_sum_A,field_sum_B,field_sum_C


with the ID I select which row i want to count.

In the fields (field_1 to field_63) is a string (A,B or C) and I want to count
how many A how many B AND how many C and update the fields field_sum_A, field_sum_B, field_sum_C.

After searching and trying for hours I ask here for help

Thanks

any time you find yourself "going sideways" across a single row, doing something to each column one after the other, it's a sign that the table is poorly designed

having 63 identical columns practically screams this point

do a search on first normal form and you will learn that instead of 63 similar columns, you'll want a separate table where there are 63 rows instead

then your query to count stuff becomes trivial

I agree, as I do not design tables on a daily base, there sure can be a lack of understanding.

The data comes from a questionary where these 63 questions are written to the table for each user one row with the 63 questions (A,B,C)

If you can give me an hint how to design that, would be very helpful.

Thanks

In this instance you'd have three table:
1) User, with a unique id to identify them
2) Question, with a unique id, to identify each
3) User_Question, no real need for a unique, single key, but a joined unique key based on User id and Question id. This table would contain the value A, B or C.

Thank you for your hint.

just one more question.

the questions are mainly radio inputs

where I have in the form f.ex



So how I would do then the combination between that 2 Tables (Question and User_question)

Where I will have

Table Question

Question_id
Question

and

Table User_question
User_Id
Question_id
Answer

You will, I presume know the identity of the user, which will give you the suer id part of the equation. From what you show I am not sure how you identify the question, but on the assumption you can (it'd be what you'd link with the field_1, field_2 parts) that gives you the other main part of the key. After that it's just down to what radio button is selected to give the value you'd insert into the User_Question table.

Thanks, got it !