1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Using count() and GROUP BY

Discussion in 'MySQL' started by asgsoft, Feb 20, 2016.

  1. #1
    hey!

    So currently I have a database with 8 columns which correspond to 8 questions. Each question can have a response ranging from 1-5 as a value.

    Currently, I am using this for each question:

    SELECT q1, count(*) FROM table GROUP BY q1
    Code (markup):
    The limitations are:
    SEMrush
    1- It will not show a 0 for a response which noone has chosen.
    2- It would be way too many queries to run if I wanted to take the values and display with php.

    Is there any other way of achieving the same thing?

    Ideally, what I would want is a count per responses per question. Something that I can then take to PHP and generate a variable with and use to populate a summary table. How best can I do this?
     
    Last edited: Feb 20, 2016
    asgsoft, Feb 20, 2016 IP
    SEMrush
  2. Zoidrums

    Zoidrums Greenhorn

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    11
    #2
    The count in only 1 table can't show you the values that are not in the table...
    To do this, you may have two other tables which contains the questions, and all the possible choices to each question.
    You'll have something like this :

    select C.choice_id, count(*)
    from questions Q,
    choices C,
    answers A
    where Q.question_id = C.question_id
    ans A.choice_id (+)= C.choice_id
    group by C.choice_id
     
    Zoidrums, Feb 9, 2017 IP