

Forum Newbie
Group: General Forum Members
Last Login: Wednesday, April 30, 2008 1:33 PM
Points: 9,
Visits: 23


I've never done anything quite like this and I'm having a major mental block, so any help appreciated. I have a table with a bunch of columns. Each column either has a 1 for a yes, 2 for a no, or 3 for NA. I need to count all the ones in the row where iAsmtID=xx and I need to count all the twos where iAsmtID=xx. Then I'll have to divide these later but for now I just need to be able to find out how many of each there are in a certain row. Thanks.




SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 11:34 AM
Points: 7,416,
Visits: 17,205


Well  you could either "longhand" the formula, or  you might consider using the UNPIVOT clause to normalize you data, and then use something like SUM(case answer when 1 then 1 else 0 end)
In other words  use UNPIVOT to turn your one row, many repeating columns into many shorter rows with no repeating columns.
Without some specific as to your table structure  I can't give you any specifics, but Books Online has some decent examples.
 Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loudspoken enduser..All right  what was my emergency again?




Forum Newbie
Group: General Forum Members
Last Login: Wednesday, April 30, 2008 1:33 PM
Points: 9,
Visits: 23


Sorry for the long code but its shorter than me trying to describe it. I am getting an error saying incorrect syntax near ",". The only comma in the string is highlighted below. What am I doing wrong?
'Open connection set conn=Server.CreateObject("ADODB.Connection") conn.open My_Conn set rs = Server.CreateObject("ADODB.Recordset") 'Get asmt information str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color=#990000]CountOfOnes, SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE ASMT.iAsmtID="&iAsmtID response.Write("DEBUG: This is the string:" + str) rs.open str, conn if rs.eof = true then response.Write(" ") response.End() else 'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos") end if rs.close set rs = nothing conn.close set conn = nothing




SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 11:34 AM
Points: 7,416,
Visits: 17,205


Here's an UNPIVT example:
select iasmtID, sum(case when answer=1 then 1 else 0 end) as countOfOnes, sum(case when answer=2 then 1 else 0 end) as countOfTwos from ( select iasmtid,question,answer from (select iasmtID,q1,q2,q3,q4,q5, q6,q7,q8,q9,q10,q11,q12, q13,q14,q15,q16,q17,q18,q19,q20 from itcc_test) t UNPIVOT (ANSWER for Question in ([q1],[q2],[q3],[q4],[q5], [q6],[q7],[q8],[q9],[q10], [q11],[q12],[q13],[q14],[q15], [q16],[q17],[q18],[q19],[q20]) ) unpvt ) as renormalizedTable WHERE iasmtID=3 group by iasmtID
 Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loudspoken enduser..All right  what was my emergency again?



