Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

data comparison SQL 1

Status
Not open for further replies.
NuclearGuy

NuclearGuy

IS-IT--Management
May 18, 2006
15
US
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

 
Sort by date Sort by votes

Change the HAVING for a WHERE and move the GROUP BY after the WHERE. [3eyes]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
Thank you for your help.
Your suggestion gave me the same result.

The answer table looks like this:

Anterior_read| Anterior_cath|Anterior_Agree| Septum_read|Septum_cath|Septum_Agree| ....
Ischemia Ischemia 1 Normal Normal 1
Normal Normal 3 Normal Normal 1
Normal Normal 1 Ischemia Ischemia 1


The table I would like to see is:

|All_agree |
54


All _ Agree = (Anterior_Agree + Septum_ Agree + Apical_Agree + InferiorApical_Agree + PosteriorLateral_Agree + Inferior_Agree)

Then I have a column that calculates the total possible Segments(Segments), I would like to sum this column. Total_Segments.

Then divide All_Agree/ Total_Segments

This percentage is what I am after.

Again I would like to thank you for your help.
 
Upvote 0 Downvote

Just add another 'Select':
Code: 
Select (Anterior_Agree + Septum_ Agree + Apical_Agree + Inferiorapical_Agree
      + Posteriorlateral_Agree + Inferior_Agree) All _ Agree
  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 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
        ((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")))
 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);
[thumbsup2]



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
Thank you for the post. There are no examples of written code there. The theory/explanation of relation tables is great, but I learn more from seeing written code.

I know what I want for an answer,(the percent). I know all the elements are achivable from the data in the table. (All_Agree/Total_Segments).

What I lack is SQL experiance.... time to read another book...

thank you
 
Upvote 0 Downvote
LKbrown great!! I am 95% there!

How do I tie these last pieces in.


Select All_Agree/Total_segments AS Percent_Agreement ***this divides one query into the other to give a percent***
From (


Select sum (segments) AS Total_segments ***this is a summed column found in the original database tlbcomments***
From (


SELECT Sum (Anterior_Agree + Septum_Agree + Apical_Agree + InferiorApical_Agree + Inferior_Agree + PosteriorLateral_Agree) AS All_agree
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 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"))));
 
Upvote 0 Downvote
Select sum (segments) AS Total_segments ***this is a summed column found in the original database tlbcomments***
From (
Click to expand...
I Don't undestand where these 'segments' comes from, please clarify.
[ponder]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
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
13 segments (default value =6)

'Segments' is another coulmn in the original table 'tblcomments'.

'Segments' has a default value of '6'.

Summing 'Segments' gives me 'Total_segments'

I would like to divide 'All_agree'/'Total_segments'
thank you
 
Upvote 0 Downvote

Maybe this:
Code: 
Select (Anterior_Agree + Septum_ Agree + Apical_Agree + Inferiorapical_Agree
      + Posteriorlateral_Agree + Inferior_Agree) All_Agree
      , (Anterior_Agree + Septum_ Agree + Apical_Agree + Inferiorapical_Agree
      + Posteriorlateral_Agree + Inferior_Agree)/Total_Segments "All_Agree/Total_Segments"
  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 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
        ((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")))
 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)
     , (Select Count(Segments) Total_Segments
          From Tlbcomments);
[morning]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
LKBrwnDBA
Here is what works:

SELECT sum (Anterior_Agree + Septum_Agree + Apical_Agree + InferiorApical_Agree + Inferior_Agree + PosteriorLateral_Agree) AS All_Agree

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 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 [%$##@_Alias];

Result: = |Total_Agree|
| 60 |

Here is what I tried based on your last response:

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

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 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 [%$##@_Alias],

(Select sum (segments) AS Total_segment
From tblComments);


I get the following error:
the SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

Thank you

 
Upvote 0 Downvote

Change this:
...PosteriorLateral_Agree) AS All_Agree / Total_segments

To this:
...PosteriorLateral_Agree) / Total_segments AS "All_Agree/Total_segments"

What is this: ... AS [%$##@_Alias], ???

Then you have: 'FROM [' with square bracket without colosing bracket; change these to parenthesis.
[3eyes]






----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
The math on the first SELECT line works.
result:
|"All_Agree/Total_Segments"|
|0.994465 |

But I am asked for the Total_Segments, I must enter by keyboard.

I tried the Total_segment string after

cath="ischemia"))))
(Select sum (segments) AS Total_segment
From tblComments);

But this dosen't work I get and ERROR. I moved it up above GROUP BY, no error but I must keyboard enter value.

here is how it looks now needing to key in the value, no errors though...

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

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,
(SELECT sum (segments) AS total_segments
FROM tblComments)

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"))));

Oh, the square brackets and AS [%$##@_Alias] are created by Access each time I save.

thank you
 
Upvote 0 Downvote
Ok,

1) You are missing a comma here:

...cath="ischemia")))),
(Select sum (segments) AS Total_segment
From tblComments);

2) Try changing this in the first select:

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




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

1)The comma did not help.
2)This segment works(the math anyway).

When I run it, it asks for Total_segments. Wants a quantity.

So it is not seeing where to calculate Total_Segments:

(SELECT sum (segments) AS total_segments
FROM tblComments)

Here is what works:

SELECT sum (Anterior_Agree + Septum_Agree + Apical_Agree + InferiorApical_Agree + Inferior_Agree + PosteriorLateral_Agree) / Total_segments

AS ["All_Agree / Total_segments"]
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 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 [%$##@_Alias];



 
Upvote 0 Downvote
Replace this:
(SELECT sum (segments) AS total_segments
FROM tblComments)
With this:
(SELECT sum (segments) FROM tblComments) AS total_segments

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
PHV, LKB
Thanks for trying, but it still errored. I did some reading, the word segment is a restricted word in SQL. I am not sure if "segment" and "segments" are considered the same. Anyway I changed the column name from "segments" to "headache".

I then tried all of the above suggestions, and it still doesn't work.... :-(
 
Upvote 0 Downvote
To go back to basics...

query1/query2 = answer
query1 (Total_agree)
query2 (Total_segments)

Both query1 and query2 are from the same table(tlbcomments).
No columns over lap.

It has got to be something simple....


 
Upvote 0 Downvote

What are the errors?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
This example no errors but it does not calculate Total_Segments it asks for it to be entered:


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

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 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 [%$##@_Alias];


[worm] worm = changes
%-( In this example I get "Syntax error in From clause"
SELECT sum (Anterior_Agree + Septum_Agree + Apical_Agree + InferiorApical_Agree + Inferior_Agree + PosteriorLateral_Agree) / Total_segments AS ["All_Agree / Total_segments"]

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, [worm]headache

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")))),[worm] sum (headache) AS Total_segments;

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
225
carp
carp
mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
208
mmiah1982
mmiah1982
fredong
  • Locked
  • Question
Update upper row data with next row data that was detached partially from import.
Replies
6
Views
263
carp
carp
jchewsmith
  • Locked
  • Question
SQL Syntax 1
Replies
1
Views
203
JarlH
JarlH
Junior6202
  • Locked
  • Question
String or binary data would be truncated.
Replies
1
Views
168
fredericofonseca
fredericofonseca

Part and Inventory Search

Sponsor

Back
Top