Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Returning a Count of A Certain Field

    I want to get a count of how many PosAnswer are associated with the QuesToAsk 'List your top 5 favorite places'

    This is the syntax I am using to achieve this, and it works, but wasn't sure if there was a better way (changing the data structure is unfortunately not an option in this situation)
    Here is my table structure
    Code:
    Create Table Test 
    (
      QuesToAsk varchar(1000),
      PosAnswer varchar(1000)   
    )
    
    INSERT INTO TEST VALUES ('List your top 5 favorite places', 'place1')
    INSERT INTO TEST VALUES ('', 'place2')
    INSERT INTO TEST VALUES ('', 'place3')
    INSERT INTO TEST VALUES ('', 'place4')
    INSERT INTO TEST VALUES ('', 'place5')
    
    INSERT INTO TEST VALUES ('Who is/was your favorite president', 'favpres')
    And this is the syntax I use to get the count I am after
    Code:
    DECLARE @VariableName varchar(25)
    DECLARE @FormattedVariableName varchar(25)
    DECLARE @FieldCount int
    
    SET @VariableName =
    (SELECT Answer
    FROM Test
    WHERE Question = 'List your top 5 favorite places')
    
    SET @FormattedVariableName = LEFT(@VariableName, LEN(@VariableName) - 1)
    
    Set @FieldCount = 
    (SELECT COUNT(Answer)
    FROM Test
    WHERE Answer Like '%' + @FormattedVariableName + '%')
    
    
    SELECT @FieldCount
    Last edited by jo15765; 11-18-14 at 08:37. Reason: forgot code tags

  2. #2
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    So, the first line in the table contains the question and an answer. The following lines have a blank for the question, and the exact same format answer only with a number on the end?

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Ok, so I made the assumption that only the records with the actual question will have a value, and that the answers will be the same format as one another, just with a different number. Here is how i did it:

    Code:
    Create Table #test 
    (
      QuesToAsk varchar(1000),
      PosAnswer varchar(1000)   
    )
    
    INSERT INTO #test VALUES ('List your top 5 favorite places', 'place1')
      ,('', 'place2')
      ,('', 'place3')
      ,('', 'place4')
      ,('', 'place5')
      ,('Who is/was your favorite president', 'favpres1')
      ,('', 'favpres2')
      ,('', 'favpres3')
      ,('', 'favpres4')
      ,('', 'favpres5')
      ,('', 'favpres6')
      ,('', 'favpres7')
    
    
    DECLARE @QAArray TABLE(QuesToAsk varchar(1000), PosAnswer varchar(1000))
    INSERT INTO @QAArray
    select QuesToAsk,LEFT(PosAnswer,LEN(PosAnswer)-1) FROM #test WHERE len(QuesToAsk)>0
    
    SELECT a.QuesToAsk,count(*) AS NumAnswers
    FROM @QAArray a
    INNER JOIN #test b
      ON a.PosAnswer = LEFT(b.PosAnswer,LEN(b.PosAnswer)-1)
    GROUP BY a.QuesToAsk

  4. #4
    Join Date
    Feb 2012
    Posts
    188
    @clawlan - yes your assumption was correct! I am away from my SQL Server installation right nwo, but will test this code as soon as I can. Thank you!!!!

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    @clawlan - That is EXACTLY what I was needing to return! Thank you kindly for the much simplified syntax and easier to follow logic!

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    No problem. I used a table variable and a function in the WHERE clause which may not be the most efficient if you have really large data sets. You can adjust as needed, but the concept remains the same. Glad I could help.

Posting Permissions

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