[EMAIL PROTECTED] wrote:
Wouldn't it be just as simple to count all 'neg', 'pos', and 'ind' values
for both columns? By the way, is there some other column you are grouping
by or is it always going to apply to entire tables of information?
I know this is not what you wanted. However, what you want still isn't
very clear to me but this might help you get better data for your
application to make decisions from:
SELECT
sum(if(status1='neg',1,0)) as neg1
,sum(if(status1='pos',1,0)) as pos1
,sum(if(status2='neg',1,0)) as neg2
,sum(if(status2='pos',1,0)) as pos2
,sum(if(status2='neg' AND status1='neg',2,0)) as neg_both
FROM dummy;
If you had another column in your data like a patientID or a groupID it
would be very simple to add that column and group by that value with this
query. As I said, what you are trying to get at is still fuzzy for me
(because you oversimplified the real issue).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Yes, you are correct. There is another column that I neglected to show
for the sake of simplicity. I guess I oversimplified things a bit and
my apologies on this.
Here is the table with the relevant columns:
SPF
idx VBS_ID SRVEIA SRVWB
1 111004 Neg Neg
2 111004 Neg Neg
3 111004 Pos Neg
4 111004 Neg Neg
5 111004 Ind Neg
6 111004 Neg Neg
7 111004 Neg Neg
I have a couple of conditions to check on:
if all rows in SRVEIA != Neg then check SRVWB
if all rows in SRVWB = Neg then count all the Negs in SRVWB and SRVEIA
else NULL
else
count all the Negs in SRVEIA
I have followed yours and Felix's example and tried creating the
following statement (not completed yet):
SELECT
SPF.VBS_ID,
SUM(if(SRVEIA='Neg',1,0)) AS SRVEIANegSum,
SUM(if(SRVEIA!='Neg',1,0)) As SRVEIANotNegSum,
SUM(if(SRVWB='Neg',1,0)) AS SRVWBNegSum,
SUM(if(SRVWB!='Neg',1,0)) As SRVWBNotNegSum,
case
when SRVWBNotNegSum = 0 then SRVEIANegSum + SRVWBNegSum
else null
end as SRVSero
FROM SPF, SPFDistinctVBSView As SPFD
where
SPF.VBS_ID = SPFD.VBS_ID
group by SPF.VBS_ID
SPFDistinctVBSView is just a view that contains the rows of unique VBS_IDs.
When I run this statement, it complains of "Unknown column
'SRVWBNotNegSum' in field list". I guess that means it does not find
such a column name in SPF. I think I am close to the solution but need
just a little nudge in the right direction.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
