SQL for computing Gini Coefficient:

SQL = "SELECT S1.GroupNumber" 'SQL = SQL & ", CAST(S1.SubjectID AS VARCHAR(10)) AS SubjectID" SQL = SQL & ", S1.SubjectID" SQL = SQL & ", S2.SubjectID AS TheirID, ScenarioID" SQL = SQL & ", (SELECT COUNT(*) FROM Chat WHERE SubjectID=S1.SubjectID AND Scenario=ScenarioID) AS MyComments" SQL = SQL & ", (SELECT COUNT(*) FROM Chat WHERE SubjectID=S2.SubjectID AND Scenario=ScenarioID) AS TheirComments" SQL = SQL & ", ABS((SELECT COUNT(*) FROM Chat WHERE SubjectID=S1.SubjectID AND Scenario=ScenarioID) -(SELECT COUNT(*) FROM Chat WHERE SubjectID=S2.SubjectID AND Scenario=ScenarioID)) AS GiniComments" SQL = SQL & ", ABS((SELECT SUM(LEN(Message)) FROM Chat WHERE SubjectID=S1.SubjectID AND Scenario=ScenarioID) -(SELECT SUM(LEN(Message)) FROM Chat WHERE SubjectID=S2.SubjectID AND Scenario=ScenarioID)) AS GiniParticipation" SQL = SQL & " INTO ##GiniTemp" SQL = SQL & " FROM Subjects S1, Subjects S2, ScenarioQuestions SQ1" SQL = SQL & " WHERE S1.GroupNumber=S2.GroupNumber AND S1.SubjectID<>S2.SubjectID" SQL = SQL & " AND SQ1.SubjectID=S1.SubjectID" SQL = SQL & " GROUP BY S1.GroupNumber, ScenarioID, S1.SubjectID, S2.SubjectID" SQL = SQL & " ORDER BY S1.GroupNumber, ScenarioID, S1.SubjectID, S2.SubjectID" Set objRS = Conn.Execute(SQL) SQL = "SELECT Groups.GroupNumber, Treatment, ScenarioQuestions.ScenarioID, COUNT(*) AS Comments" SQL = SQL & ", SUM(LEN(Message)) AS Particip" 'SQL = SQL & ", (SELECT SUM(GiniComments) FROM ##GiniTemp WHERE ##GiniTemp.GroupNumber=Groups.GroupNumber AND ##GiniTemp.ScenarioID=ScenarioQuestions.ScenarioID) AS GiniSum" 'SQL = SQL & ", (SELECT COUNT(*) FROM Subjects WHERE Subjects.GroupNumber=Groups.GroupNumber) AS GiniN" SQL = SQL & ", (SELECT SUM(GiniComments) FROM ##GiniTemp WHERE ##GiniTemp.GroupNumber=Groups.GroupNumber AND ##GiniTemp.ScenarioID=ScenarioQuestions.ScenarioID)/(2*POWER((SELECT COUNT(*) FROM Subjects WHERE Subjects.GroupNumber=Groups.GroupNumber),2)*(CAST(COUNT(*) AS NUMERIC(12,4))/(SELECT COUNT(*) FROM Subjects WHERE Subjects.GroupNumber=Groups.GroupNumber))) AS GiniComm" SQL = SQL & ", (SELECT SUM(GiniParticipation) FROM ##GiniTemp WHERE ##GiniTemp.GroupNumber=Groups.GroupNumber AND ##GiniTemp.ScenarioID=ScenarioQuestions.ScenarioID)/(2*POWER((SELECT COUNT(*) FROM Subjects WHERE Subjects.GroupNumber=Groups.GroupNumber),2)*(CAST(SUM(LEN(Message)) AS NUMERIC(12,4))/(SELECT COUNT(*) FROM Subjects WHERE Subjects.GroupNumber=Groups.GroupNumber))) AS GiniPart" SQL = SQL & ", AVG(cast((me1+me2+me3+me4) as decimal(4,2))/4) AS ME" SQL = SQL & ", AVG(cast((beh1+beh2+(8-beh3)) as decimal(4,2))/3) AS BEH" SQL = SQL & ", AVG(cast(((8-mo1)+mo2+mo3) as decimal(4,2))/3) AS MO" SQL = SQL & ", AVG(cast((pi1+pi2+pi3+pi4) as decimal(4,2))/4) AS PI" SQL = SQL & ", AVG(cast((pime1+pime2+pime3+pime4) as decimal(4,2))/4) AS PIME" SQL = SQL & ", AVG(cast((pibeh1+pibeh2+(8-pibeh3)) as decimal(4,2))/3) AS PIBEH" SQL = SQL & ", AVG(cast(((8-pimo1)+pimo2+pimo3) as decimal(4,2))/3) AS PIMO" SQL = SQL & ", AVG(cast((pipi1+pipi2+pipi3+pipi4) as decimal(4,2))/4) AS PIPI" SQL = SQL & ", AVG(cast((pime1+pime2+pime3+pime4)-(me1+me2+me3+me4) as decimal(4,2))/4) AS DeltaME" SQL = SQL & ", AVG(cast((pibeh1+pibeh2+(8-pibeh3))-(beh1+beh2+(8-beh3)) as decimal(4,2))/3) AS DeltaBEH" SQL = SQL & ", AVG(cast(((8-pimo1)+pimo2+pimo3)-((8-mo1)+mo2+mo3) as decimal(4,2))/3) AS DeltaMO" SQL = SQL & ", AVG(cast((pipi1+pipi2+pipi3+pipi4)-(pi1+pi2+pi3+pi4) as decimal(4,2))/4) AS DeltaPI" SQL = SQL & " FROM GROUPS, Subjects, ScenarioQuestions, Chat" SQL = SQL & " WHERE GROUPS.GroupNumber=Subjects.GroupNumber" SQL = SQL & " AND Subjects.SubjectID=ScenarioQuestions.SubjectID" SQL = SQL & " AND Chat.SubjectID=Subjects.SubjectID AND Chat.Scenario=ScenarioQuestions.ScenarioID" 'SQL = SQL & " AND ##GiniTemp.GroupNumber=Groups.GroupNumber AND ##GiniTemp.ScenarioID=ScenarioQuestions.ScenarioID" SQL = SQL & " GROUP BY Groups.GroupNumber, Treatment, ScenarioQuestions.ScenarioID" SQL = SQL & " ORDER BY Groups.GroupNumber, ScenarioQuestions.ScenarioID" Set objRS = Conn.Execute(SQL)