data comparison SQL 1

[NuclearGuy]

I'm a beginner with SQL started on monday.
I could use some help.


My table:
column name
1 Anterior_read
2 Septum_read
3 Apical_ read
4 InferiorApical_read
5 PosteriorLaterial_read
6 Inferior_read
7 Anterior_cath
8 Septum_cath
9 Apical_ cath
10 InferiorApical_cath
11 PosteriorLaterial_cath
12 Inferior_cath

DATA in each row: Normal Scar Ischemia


What I want to know:
1) What is the percent correlation between reader (read) and cardiac Catheterization (cath)

(column 1 = column 7)+ (column 2 = column 8)+ (column 3= column 9)+ (column 4 = column 10)+ (column 5 = column 11)+ (column 6= column 12) = total "agree segments" / total possible agreements

example1: Anterior_Read Septum_Read Apical_Read InferiorApical_read PosteriorLateral_read Inferior_read
Normal Scar Scar Normal Normal Ischemia

Anterior_cath Septum_cath Apical_cath InferiorApical_cath PosteriorLateral_cath Inferior_cath
Normal Scar Scar Normal Normal Ischemia

if we compare: Anterior_read to anterior_cath (normal = normal) = 1
Septum_read to Septum_cath (scar = scar) = 1
Apical_Read to Apical_cath (scar = scar) = 1
InferiorApical_read to InferiorApical_cath (normal = normal) = 1
PosteriorLateral_read to PosteriorLateral_cath (normal = normal) = 1
Inferior_read to Inferior_cath (Ischemia = Ischemia) =1

There were 6 possible comparisons 6 out of a possible 6 = 6/6 = 100% correlation.



example2: Anterior_Read Septum_Read Apical_Read InferiorApical_read PosteriorLateral_read Inferior_read
Normal Scar Scar Normal Normal Ischemia

Anterior_cath Septum_cath Apical_cath InferiorApical_cath PosteriorLateral_cath Inferior_cath
Normal Normal Normal Normal Normal Normal

if we compare: Anterior_read to anterior_cath (normal = normal) = 1
Septum_read to Septum_cath (scar = Normal) = 0
Apical_Read to Apical_cath (scar = normal) = 0
InferiorApical_read to InferiorApical_cath (normal = normal) = 1
PosteriorLateral_read to PosteriorLateral_cath (normal = normal) = 1
Inferior_read to Inferior_cath (Ischemia = Normal) =0

There were 6 possible comparisons 3 out of a possible 6 = 3/6 = 50% correlation.

What I have written so far:

SELECT Anterior_read, Anterior_cath, Sum(1) AS Anterior_Agree, Septum_read, Septum_cath, Sum(1) AS Septum_Agree, Apical_read, Apical_cath, Sum(1) AS Apical_Agree, InferiorApical_read, InferiorApical_cath, Sum(1) AS InferiorApical_Agree, Inferior_read, Inferior_cath, Sum(1) AS Inferior_Agree, PosteriorLateral_read, PosteriorLateral_cath, Sum(1) AS PosteriorLateral_Agree

FROM tblComments

GROUP BY Anterior_read, Anterior_cath, Septum_read, Septum_cath, Apical_read, Apical_cath, InferiorApical_read, InferiorApical_cath, Inferior_read, Inferior_cath, PosteriorLateral_read, PosteriorLateral_cath

HAVING (((Anterior_read="normal") AND (Anterior_cath="Normal") OR
(Anterior_read="scar") AND (Anterior_cath="scar") OR
(Anterior_read="ischemia") AND (Anterior_cath="ischemia")) AND

((Septum_read="normal") AND (Septum_cath="Normal") OR
(Septum_read="scar") AND (Septum_cath="scar") OR
(Septum_read="ischemia") AND (Septum_cath="ischemia")) AND

((Apical_read="normal") AND (Apical_cath="Normal") OR
(Apical_read="scar") AND (Apical_cath="scar") OR
(Apical_read="ischemia") AND (Apical_cath="ischemia")) AND

((InferiorApical_read="normal") AND (InferiorApical_cath="Normal") OR
(InferiorApical_read="scar") AND (InferiorApical_cath="scar") OR
(InferiorApical_read="ischemia") AND (InferiorApical_cath="ischemia")) AND

((Inferior_read="normal") AND (Inferior_cath="Normal") OR
(Inferior_read="scar") AND (Inferior_cath="scar") OR
(Inferior_read="ischemia") AND (Inferior_cath="ischemia")) AND

((PosteriorLateral_read="normal") AND (PosteriorLateral_cath="Normal") OR
(PosteriorLateral_read="scar") AND (PosteriorLateral_cath="scar") OR
(PosteriorLateral_read="ischemia") AND (PosteriorLateral_cath="ischemia")));


Thanks for your help!

Thank you,
stu

 

[LKBrwnDBA]

Change this:
...
(PosteriorLateral_read="ischemia") AND (PosteriorLateral_cath="ischemia")))), sum (headache) AS Total_segments;

To this:

(PosteriorLateral_read="ischemia") AND (PosteriorLateral_cath="ischemia")))),
(SELECT sum (headache) FROM tblComments
AS Total_segments);



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[NuclearGuy]

I get the following error:
"Cannot repeat table name '%$##@_Alias' in FROM clause.

 

[LKBrwnDBA]

Remove that part: ". AS [%$##@_Alias];"



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[NuclearGuy]

I have run the query with and without this statement. I get the same error.

I am using Microsoft Access 2002. It adds the the ". AS [%$##@_Alias];"

I am not sure why.

 

[LKBrwnDBA]

Ok, maybe you need to use PARENTESIS instead of square brackets to envelope your SELECT statement (and remove that ". AS [%$##@_Alias];" part.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[NuclearGuy]

I prayed to the hockey Gods last night to help... not sure if it would help, has not helped my Bruins yet.

Here is what I have tried:
1)I got rid of AS [%$##@_Alias] by labeling it A_Name.
2)I moved the 2nd query up. The 2 queries are concurrent, not dependent upon one another, so I want to try the 2nd query first.

It now give me: "Syntax error in JOIN operation"



SELECT sum (Anterior_Agree + Septum_Agree + Apical_Agree + InferiorApical_Agree + Inferior_Agree + PosteriorLateral_Agree) / Total_segments AS ["All_Agree / Total_segments"]

FROM
((SELECT Sum (headache) AS Total_segments
FROM tblComments)

(SELECT Anterior_read, Anterior_cath, Sum(1) AS Anterior_Agree, Septum_read, Septum_cath, Sum(1) AS Septum_Agree, Apical_read, Apical_cath, Sum(1) AS Apical_Agree, InferiorApical_read, InferiorApical_cath, Sum(1) AS InferiorApical_Agree, Inferior_read, Inferior_cath, Sum(1) AS Inferior_Agree, PosteriorLateral_read, PosteriorLateral_cath, Sum(1) AS PosteriorLateral_Agree

FROM tblComments

GROUP BY Anterior_read, Anterior_cath, Septum_read, Septum_cath, Apical_read, Apical_cath, InferiorApical_read, InferiorApical_cath, Inferior_read, Inferior_cath, PosteriorLateral_read, PosteriorLateral_cath

HAVING (((Anterior_read="normal") AND (Anterior_cath="Normal") OR
(Anterior_read="scar") AND (Anterior_cath="scar") OR
(Anterior_read="ischemia") AND (Anterior_cath="ischemia")) AND

((Septum_read="normal") AND (Septum_cath="Normal") OR
(Septum_read="scar") AND (Septum_cath="scar") OR
(Septum_read="ischemia") AND (Septum_cath="ischemia")) AND

((Apical_read="normal") AND (Apical_cath="Normal") OR
(Apical_read="scar") AND (Apical_cath="scar") OR
(Apical_read="ischemia") AND (Apical_cath="ischemia")) AND

((InferiorApical_read="normal") AND (InferiorApical_cath="Normal") OR
(InferiorApical_read="scar") AND (InferiorApical_cath="scar") OR
(InferiorApical_read="ischemia") AND (InferiorApical_cath="ischemia")) AND

((Inferior_read="normal") AND (Inferior_cath="Normal") OR
(Inferior_read="scar") AND (Inferior_cath="scar") OR
(Inferior_read="ischemia") AND (Inferior_cath="ischemia")) AND

((PosteriorLateral_read="normal") AND (PosteriorLateral_cath="Normal") OR
(PosteriorLateral_read="scar") AND (PosteriorLateral_cath="scar") OR
(PosteriorLateral_read="ischemia") AND (PosteriorLateral_cath="ischemia"))))) AS A_NAME;

 

[LKBrwnDBA]

You are missing a comma and added too many parentesis, you query should look something like this:

SELECT sum (Anterior_Agree + Septum_Agree +  Apical_Agree + InferiorApical_Agree + Inferior_Agree + PosteriorLateral_Agree)  / Total_segments AS ["All_Agree / Total_segments"]

FROM (SELECT Sum (headache) AS Total_segments
        FROM tblComments)[red],[/red]
     (SELECT Anterior_read, Anterior_cath, Sum(1) AS Anterior_Agree, BLAH...BLAH..BLAH
        FROM tblComments
       WHERE (((Anterior_read="normal") AND
               (Anterior_cath="Normal") OR 
               (Anterior_read="scar") AND 
               (Anterior_cath="scar") OR 
               (Anterior_read="ischemia") AND
               (Anterior_cath="ischemia"))  
         AND ...ETC...ETC...
                PosteriorLateral_read="ischemia") AND 
               (PosteriorLateral_cath="ischemia")) 
    GROUP BY Anterior_read, Anterior_cath, Septum_read,
             Septum_cath, Apical_read, Apical_cath,  
             InferiorApical_read, InferiorApical_cath, 
             Inferior_read, Inferior_cath, 
             PosteriorLateral_read, PosteriorLateral_cath)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[NuclearGuy]

Here is what I have now... when I run the query it asks me for the [t.segments].

After I enter the number I notice 2 things wrong.
1) It multiplies the answer by the number I entered.
Example:

When 1 is entered:
|Total_agree| Total_segments|Value|
90 6 15

When 2 is entered:

|Total_agree| Total_segments|Value|
90 12 7.5

When 3 is entered:
|Total_agree| Total_segments|Value|
90 18 5

The 90 is correct, and the Value column is calculating correctly.

2) The correct answer for Total segments is 162.
6 = the number of segments in each row of data. So it is not summing the column (6+6+6+6) or (27row x 6=162).

I have seen and read several links on this site:

http://www.tek-tips.com/viewthread.cfm?qid=1189235

http://www.tek-tips.com/viewthread.cfm?qid=1202474

These are both similar, except they pull data from 2 tables. I have one table. But my query does not see the column segments.



SELECT Sum(Anterior_Agree+Septum_Agree+Apical_Agree+InferiorApical_Agree+Inferior_Agree+PosteriorLateral_Agree) AS Total_agree,

Sum(t.segments) AS Total_segments, [Total_agree]/[Total_segments] AS [Value]

FROM [SELECT Anterior_read, Anterior_cath, Sum(1) AS Anterior_Agree, Septum_read, Septum_cath, Sum(1) AS Septum_Agree, Apical_read, Apical_cath, Sum(1) AS Apical_Agree, InferiorApical_read, InferiorApical_cath, Sum(1) AS InferiorApical_Agree,

Inferior_read, Inferior_cath, Sum(1) AS Inferior_Agree, PosteriorLateral_read, PosteriorLateral_cath, Sum(1) AS PosteriorLateral_Agree

FROM t

GROUP BY Anterior_read, Anterior_cath, Septum_read, Septum_cath, Apical_read, Apical_cath, InferiorApical_read, InferiorApical_cath, Inferior_read, Inferior_cath, PosteriorLateral_read, PosteriorLateral_cath

HAVING (((Anterior_read="normal") AND (Anterior_cath="Normal") OR
(Anterior_read="scar") AND (Anterior_cath="scar") OR
(Anterior_read="ischemia") AND (Anterior_cath="ischemia")) AND

((Septum_read="normal") AND (Septum_cath="Normal") OR
(Septum_read="scar") AND (Septum_cath="scar") OR
(Septum_read="ischemia") AND (Septum_cath="ischemia")) AND

((Apical_read="normal") AND (Apical_cath="Normal") OR
(Apical_read="scar") AND (Apical_cath="scar") OR
(Apical_read="ischemia") AND (Apical_cath="ischemia")) AND

((InferiorApical_read="normal") AND (InferiorApical_cath="Normal") OR
(InferiorApical_read="scar") AND (InferiorApical_cath="scar") OR
(InferiorApical_read="ischemia") AND (InferiorApical_cath="ischemia")) AND

((Inferior_read="normal") AND (Inferior_cath="Normal") OR
(Inferior_read="scar") AND (Inferior_cath="scar") OR
(Inferior_read="ischemia") AND (Inferior_cath="ischemia")) AND

((PosteriorLateral_read="normal") AND (PosteriorLateral_cath="Normal") OR
(PosteriorLateral_read="scar") AND (PosteriorLateral_cath="scar") OR
(PosteriorLateral_read="ischemia") AND (PosteriorLateral_cath="ischemia")))]. AS car;