Thread: SQL Question

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,140
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0

    Design ?


    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,441
    Rep Power
    1688
    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.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Thank you for your hint.

    just one more question.

    the questions are mainly radio inputs

    where I have in the form f.ex

    Code:
    <INPUT type="radio" name="Question" value="A"> Answer A<BR>
    <INPUT type="radio" name="Question" value="B"> Anser B<BR>
    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




    Originally Posted by SimonJM
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,441
    Rep Power
    1688
    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.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    4
    Rep Power
    0
    Thanks, got it !

    Originally Posted by SimonJM
    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.

IMN logo majestic logo threadwatch logo seochat tools logo