MySQL Forums
Forum List  »  Newbie

Re: Count not returning correct value
Posted by: Fionn MacHugh
Date: August 03, 2005 08:08AM

Thanks Andrew,

Yep i think ive managed to get it like this

select A.ref, A.seriesid,

(select count(seriesid) from `power`.`rights` B
where B.seriesid = A.seriesid and
((underlying is Null and area = '') or (underlying is Null and area = 'BGR') or
(underlying is Null and area ='CEE'))) as CNT

from `power`.`rights` A
where
((underlying is Null and area = '') or (underlying is Null and area = 'BGR') or
(underlying is Null and area ='CEE'))

this is giving me a count based on the first selection called CNT
and returning records from the second selection

i wanted all the records where the 'where' was satisfied
and a count of how many times that seriesid occured
in the same result set - which seems to be happening now

now i have to qualify that by saying where the count = 1 return the record
where count = 2 then only return the record where area field is not null
and where count = 3 return only when startdate is the most recent (most like 0?)

so mm getting there - but quite a statement at the end - nice to make it simpler!

cheers for all and any more help!!

Options: ReplyQuote


Subject
Written By
Posted
Re: Count not returning correct value
August 03, 2005 08:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.