Counting of subCount

[Michali]

Hello all
I am having dificulties of putting this sql formula into a crystal report.
when i am trying to use a running total field i cant use another running total.

thank you for a quick advice

select count(subcount)from (
select count(str_person_id) subcount from (
select c.str_person_id
from ent_card c, ent_card_events e
where c.int_card_key=e.int_card_key
and e.int_card_status_key=4
)
group by str_person_id
)
where subcount=1

 

[synapsevampire]

What version of Crystal are you using, and what connectivity?

You can just paste the SQL in to CR 9, or use ADO and paste it in using the SQL option.

What I believe your SQL would return is at most a listing of just 1's.

Doesn't seem very meaningful to me, wouldn't you want some descriptive information returned too, such as the person_id or something?

You might represent your question by posting example data and expected output, rather than SQL. Many strong Crystal folk aren't well versed in SQL, but they can figure out a slick way to represent data.

-k

 

[Michali]

Hello syn
thank u for your reply.
i am using cr 8.5

i only need to get the final total of how many people have one card.
so the first select gives me a result like
str_person_id
00111
00112
00111
00111
00113
00113

the second select gives me:
subcount (id grouping)
3 (00111)
2 (00113)
1 (00112)

the last select gives me :1
meaning only one peson_id appears once - i dont need to show the person's id.

thank u
michal