Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Counting in rows Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2008 10:42 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.
Post #449010
Posted Tuesday, January 29, 2008 11:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 7,119, Visits: 15,005
Well - you could either "long-hand" 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 loud-spoken end-user..All right - what was my emergency again?
Post #449033
Posted Thursday, January 31, 2008 8:01 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Post #449960
Posted Thursday, January 31, 2008 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 7,119, Visits: 15,005
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 loud-spoken end-user..All right - what was my emergency again?
Post #449986
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse