Questions

T-SQL: How to Select Rows By Certain Sum of a Column?

Tags:
+
0 Votes
Locked

T-SQL: How to Select Rows By Certain Sum of a Column?

StojanMk
Hi everyone. I have a table named as Questions with 3 columns(Id int, Question_Text varchar(200), Question_Points tinyint).
I need to select random selection with a condition the sum of Question_Points to be certain number(ex: sum(Question_Points) = 150). This should be actually generating test by total test points.
  • +
    0 Votes
    Slayer_

    It sounds like you are saying

    <pre>
    Select * from Questions Where Question_Points=150
    </pre>

    +
    0 Votes
    StojanMk

    The trick is: the sum(Question_Points) = 150. For example, lets say Question_Points of the 1st row is 10, Question_Points of the 2nd row is 20, of the 3rd row is 15, and randomly so on. So, the sum of the selection need to be 150.

    Greetings

    +
    0 Votes
    Slayer_

    So are you trying to select question text that has a sum of 150?
    I hope these are in two different tables then.

    Im a it rusty, but I think its something like

    Select Question_Text, sum(Question_Points) From Questions Group by Question_Text

    That should give you the total points for each question. I cannot remember how to narrow it down further.

    +
    0 Votes
    StojanMk

    I'll give you detailed example. Lets say the table is like this:
    Id Question_Text Question_Points
    1 Question1 5
    2 Question2 5
    3 Question3 10
    4 Question4 5
    5 Question5 5
    Can we make elegant selection where sum(Question_Points) = 20, and the result set would be like this:
    1 Question1 5
    2 Question2 5
    3 Question3 10
    [5+5+10=20] , Or the result set would be like this:
    3 Question3 10
    4 Question4 5
    5 Question5 5
    [10+5+5=20] The sum is again 20, or the result set would be like this:
    1 Question1 5
    2 Question2 5
    4 Question4 5
    5 Question5 5
    The sum is again 20. I'll emphasize again that the result selection should be randomly chosen. Hope better explained n 10x 4 ur attention.

  • +
    0 Votes
    Slayer_

    It sounds like you are saying

    <pre>
    Select * from Questions Where Question_Points=150
    </pre>

    +
    0 Votes
    StojanMk

    The trick is: the sum(Question_Points) = 150. For example, lets say Question_Points of the 1st row is 10, Question_Points of the 2nd row is 20, of the 3rd row is 15, and randomly so on. So, the sum of the selection need to be 150.

    Greetings

    +
    0 Votes
    Slayer_

    So are you trying to select question text that has a sum of 150?
    I hope these are in two different tables then.

    Im a it rusty, but I think its something like

    Select Question_Text, sum(Question_Points) From Questions Group by Question_Text

    That should give you the total points for each question. I cannot remember how to narrow it down further.

    +
    0 Votes
    StojanMk

    I'll give you detailed example. Lets say the table is like this:
    Id Question_Text Question_Points
    1 Question1 5
    2 Question2 5
    3 Question3 10
    4 Question4 5
    5 Question5 5
    Can we make elegant selection where sum(Question_Points) = 20, and the result set would be like this:
    1 Question1 5
    2 Question2 5
    3 Question3 10
    [5+5+10=20] , Or the result set would be like this:
    3 Question3 10
    4 Question4 5
    5 Question5 5
    [10+5+5=20] The sum is again 20, or the result set would be like this:
    1 Question1 5
    2 Question2 5
    4 Question4 5
    5 Question5 5
    The sum is again 20. I'll emphasize again that the result selection should be randomly chosen. Hope better explained n 10x 4 ur attention.