Professional Web Applications Themes

select a column with a condition - MySQL

I need to know if there is a way to work with conditions on colums... e.g. how can I take the sum of all "answer" columns? Or how can I select the score of e.g. only question7 (in combination with a query on a question-table?) Don't ask about the table structure... I didn't design it, and it's not possible to change it... CREATE TABLE `answer` ( `individualId` int(11) NOT NULL default '0', `testId` int(11) NOT NULL default '0', `question1` decimal(3,2) NOT NULL default '0.00', `question2` decimal(3,2) NOT NULL default '0.00', `question3` decimal(3,2) NOT NULL default '0.00', `question4` decimal(3,2) NOT NULL ...

  1. #1

    Default select a column with a condition

    I need to know if there is a way to work with conditions on colums...
    e.g. how can I take the sum of all "answer" columns? Or how can I
    select the score of e.g. only question7 (in combination with a query on
    a question-table?)

    Don't ask about the table structure... I didn't design it, and it's not
    possible to change it...

    CREATE TABLE `answer` (
    `individualId` int(11) NOT NULL default '0',
    `testId` int(11) NOT NULL default '0',
    `question1` decimal(3,2) NOT NULL default '0.00',
    `question2` decimal(3,2) NOT NULL default '0.00',
    `question3` decimal(3,2) NOT NULL default '0.00',
    `question4` decimal(3,2) NOT NULL default '0.00',
    `question5` decimal(3,2) NOT NULL default '0.00',
    `question6` decimal(3,2) NOT NULL default '0.00',
    `question7` decimal(3,2) NOT NULL default '0.00',
    `question8` decimal(3,2) NOT NULL default '0.00',
    `question9` decimal(3,2) NOT NULL default '0.00',
    PRIMARY KEY (`individualId`,`testId`)
    ) ENGINE=MyISAM DEFAULT CHT=utf8;

    Thanks!

    gehegeradeaus@gmail.com Guest

  2. #2

    Default Re: select a column with a condition


    com wrote:
     

    SELECT question1+question2... FROM answer ORDER BY individualId ?
    SELECT sum(question7) FROM table WHERE...

    strawberry Guest

  3. #3

    Default Re: select a column with a condition


    strawberry schreef:
     
    >
    > SELECT question1+question2... FROM answer ORDER BY individualId ?
    > SELECT sum(question7) FROM table WHERE...[/ref]

    Thanks for your answer, but that's not exactly what I'm searching for :

    I need this :

    SELECT question.questionId, individual.indivudal,
    EVAL('answer.question'.question.questionId) AS score
    FROM question, individual , answer
    WHERE answer.individualId = question.individualId

    so when question.questionId = 1 -> the query will return the score for
    question1

    gehegeradeaus@gmail.com Guest

  4. #4

    Default Re: select a column with a condition

    com wrote: 

    Does this do what you need?

    SELECT q.questionId, i.individual,
    CASE q.questionId
    WHEN 1 THEN a.question1
    WHEN 2 THEN a.question2
    WHEN 3 THEN a.question3
    WHEN 4 THEN a.question4
    WHEN 5 THEN a.question5
    WHEN 6 THEN a.question6
    WHEN 7 THEN a.question7
    WHEN 8 THEN a.question8
    WHEN 9 THEN a.question9
    END AS score
    FROM question AS q
    JOIN individual AS i
    JOIN answer AS a ON USING (individualId);

    Regards,
    Bill K.
    Bill Guest

  5. #5

    Default Re: select a column with a condition


    Bill Karwin schreef:
     
    >
    > Does this do what you need?
    >
    > SELECT q.questionId, i.individual,
    > CASE q.questionId
    > WHEN 1 THEN a.question1
    > WHEN 2 THEN a.question2
    > WHEN 3 THEN a.question3
    > WHEN 4 THEN a.question4
    > WHEN 5 THEN a.question5
    > WHEN 6 THEN a.question6
    > WHEN 7 THEN a.question7
    > WHEN 8 THEN a.question8
    > WHEN 9 THEN a.question9
    > END AS score
    > FROM question AS q
    > JOIN individual AS i
    > JOIN answer AS a ON USING (individualId);
    >
    > Regards,
    > Bill K.[/ref]

    Yes it does, thanks!

    gehegeradeaus@gmail.com Guest

Similar Threads

  1. Hyperlink template column and change link text based on Condition
    By Paul D. Fox in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: June 16th, 05:25 PM
  2. Select a COLUMN in a datagrid?
    By Roger Lainson in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 9th, 11:20 PM
  3. Getting column name in Select statement
    By Jane in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 8th, 04:17 PM
  4. Button Select Column
    By mark in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: July 5th, 10:57 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139