March 31, 2010 at 5:37 am
I have a questionnaire, with a fixed number of questions, and answers can be yes/now. What would the code look like to get a count both the yes and no answers (1 or 0), i would need the result to be in a list format. ie
If tried to group by one side, and find the opposite result by deduction, but if there are no positives or negatives i get no result. So i'm missing something basic.
Thanks in advance.
March 31, 2010 at 6:01 am
It would be very helpful to see what your tables look like.
Anyway, without any other information, all I can tell you is something like this:
SELECT answer, COUNT(*) AS answer_count
FROM answers
WHERE questionaire_id = @id
GROUP BY answer
Take a look at the article linked in my signature and please post back table scripts and desired output for a better reply.
-- Gianluca Sartori
March 31, 2010 at 6:08 am
SELECT
SUM(CASE WHEN answer=1 THEN 1 ELSE 0 END) As YES_Answers,
SUM(CASE WHEN answer=0 THEN 1 ELSE 0 END) As NO_Answers,
FROM answers
WHERE questionaire_id = @id
Lowell
March 31, 2010 at 6:40 am
First many thanks for the really quick reply.
Here's the code. While the second reply will get me an answer, results are in 2 colums. Need them as a list.
Problem with other approach, first reply (and mine), is it is not presenting a result when the count is zero. For me a count of zero zero's is a valuable result.
SELECT dbo.tbl_Results.User_ID, dbo.tbl_Questions.Answer AS question_type, dbo.tbl_Questions.Category_ID, dbo.tbl_Results.Result,
COUNT(dbo.tbl_Results.Result) AS count_positive
FROM dbo.tbl_Categories INNER JOIN
dbo.tbl_Questions ON dbo.tbl_Categories.ID = dbo.tbl_Questions.Category_ID INNER JOIN
dbo.tbl_Results ON dbo.tbl_Questions.ID = dbo.tbl_Results.Question_ID
GROUP BY dbo.tbl_Results.User_ID, dbo.tbl_Questions.Answer, dbo.tbl_Questions.Category_ID, dbo.tbl_Results.Result
HAVING dbo.tbl_Results.User_ID=17
ORDER BY dbo.tbl_Results.User_ID
March 31, 2010 at 7:03 am
Try using LEFT join instead of INNER JOIN, assigning a predetermined value to NULL answers (-1 in my example):
SELECT dbo.tbl_Results.User_ID,
dbo.tbl_Questions.Answer AS question_type,
dbo.tbl_Questions.Category_ID,
dbo.tbl_Results.Result,
COUNT(ISNULL(dbo.tbl_Results.Result,-1)) AS count_positive
FROM dbo.tbl_Categories
INNER JOIN dbo.tbl_Questions
ON dbo.tbl_Categories.ID = dbo.tbl_Questions.Category_ID
LEFT JOIN dbo.tbl_Results
ON dbo.tbl_Questions.ID = dbo.tbl_Results.Question_ID
GROUP BY dbo.tbl_Results.User_ID,
dbo.tbl_Questions.Answer,
dbo.tbl_Questions.Category_ID,
dbo.tbl_Results.Result
HAVING dbo.tbl_Results.User_ID = 17
ORDER BY dbo.tbl_Results.User_ID
-- Gianluca Sartori
March 31, 2010 at 8:08 am
Gianluca Sartori (3/31/2010)
Try using LEFT join instead of INNER JOIN, assigning a predetermined value to NULL answers (-1 in my example):
No that did not work. But i see the issue more clearly now. For a single category there are 15 records with either 1 or 0. If they are counted and all contain the same value then there can only be a single result, for the aggregate function.
Any other ideas welcome.
March 31, 2010 at 8:14 am
No, no other ideas without table scripts, sample data and desired output. It's a shot in the dark this way.
Please see the article in my signature.
Once you provided more info, I'll be glad to help.
-- Gianluca Sartori
March 31, 2010 at 8:33 am
Thanks for help.
I understand now that i have to work around, but have an answer if I use sum in place of count, and remove the results field. Gives me one side (the positive scores), then can derive the negatives (15-result) Could do a pivot to get in list, but I might get an easier solution when i look at the derived views.
Cheers.
March 31, 2010 at 9:33 am
fish-1021547 (3/31/2010)
but I might get an easier solution when i look at the derived views.
Or you might get an easier, BETTER solution if you were to provide the CREATE TABLE scripts and INSERT scripts for sample data, which has been asked twice previously. See the link previously referenced, or the first link in my signature. It's always best to also include what your expected output would be, based upon the sample data provided.
There have been several people willing to help you get exactly what you need... all you need to do is to help us help you by providing the requested data.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 31, 2010 at 11:48 am
Thanks again.
Here's the code i ended with. (bit raw and needs tidying but works).
(code has been simplified for ease of reading, and left out the obvious stuff. Changed my boolean into int, so i could sum the '1's.
View 2
code has been altered for ease of use an
SELECT
dbo.tbl_Results.User_ID,
dbo.tbl_Questions.Answer AS question_type,
dbo.tbl_Questions.Category_ID,
SUM(dbo.tbl_Results.Result) AS Count_positive
FROM
dbo.tbl_Categories INNER JOIN dbo.tbl_Questions ON dbo.tbl_Categories.ID = dbo.tbl_Questions.Category_ID INNER JOIN
dbo.tbl_Results ON dbo.tbl_Questions.ID = dbo.tbl_Results.Question_ID
GROUP BY dbo.tbl_Results.User_ID, dbo.tbl_Questions.Answer, dbo.tbl_Questions.Category_ID
ORDER BY dbo.tbl_Results.User_ID, question_type, dbo.tbl_Questions.Category_ID
Output
User_IDquestion_typeCategory_IDCount_positive
17C 10
17C 29
17C 39
17C 415
17C 58
17C 67
View 3
SELECT User_ID, question_type, MAX(one) AS cat_1L, MAX(two) AS cat_2L, MAX(three) AS cat_3L, MAX(four) AS cat_4L, MAX(five) AS cat_5L, MAX(six) AS cat_6L
FROM (SELECT User_ID, question_type, CASE WHEN category_id = 1 THEN count_positive ELSE NULL END AS one,
CASE WHEN category_id = 2 THEN count_positive ELSE NULL END AS two, CASE WHEN category_id = 3 THEN count_positive ELSE NULL END AS three,
CASE WHEN category_id = 4 THEN count_positive ELSE NULL END AS four, CASE WHEN category_id = 5 THEN count_positive ELSE NULL END AS five,
CASE WHEN category_id = 6 THEN count_positive ELSE NULL END AS six
FROM dbo.View_2) AS x
GROUP BY User_ID, question_type
View 4
SELECT User_ID,
question_type,
cat_1L AS a
cat_2L AS b,
cat_3L AS c,
cat_4L AS d,
cat_5L AS e,
cat_6L AS f,
15 - cat_1L AS g,
15 - cat_2L AS h,
15 - cat_3L AS k,
15 - cat_4L AS l,
15 - cat_5L AS m,
15 - cat_6L AS n
FROM dbo.View_3
And here's the unpivot
SELECT USER_ID, question_type, element, result
FROM (SELECT user_id, question_type, [a], , [c], [d], [e], [f], [g], [hn], , [j],
[k], [le], [m], [n], [o], [p],
, [r]
FROM [OXIS].[dbo].[View_4]) p UNPIVOT (result FOR element IN ([a], , [c], [d], [e], [f], [g],
[h], , [j], [k], [l], [m], [m], [o], [p],
, [r])) AS unpvt
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply