Assigning/Storing Count values

Hi!

Here's what I'm trying to do:

I'd like to calculate the value of a recurring attribute and then use it in
an artithmetic statement.

My original equation was

select
v1.medicareid, (a1.datedischarged - a1.dateadmitted)*500 + count(v1.medicareid)*d1.visitfee
from
admission a1, doctor d1, visit v1
where
v1.medicareid = 'abcdefghik' and
a1.medicareid = v1.medicareid and
d1.eid = v1.doctorid;

Everything works fine until count(v1.medicareid)*d1.visitfee (I tried it without this statement).

But when I add the above statement it outputs an ORA-00936 error, i.e. that the beginning of the line is not a single-group group function.

What I tried next is to separate the two statements and have the date calculation be performed at a later time and first, I'd calculate the occurrence of the medicare id using:

select
count(v1.medicareid)
from
visit v1
where
v1.medicareid = 'theidnumber';

How on earth do you store this result??

I tried assigning the entire statement as an insert value to no avail.

Basically if I could store it in some table, then I could retrieve it later. Better yet if I could store it in some variable, but we can't do that outside a table in sqlplus, or can we?

You might try something like:

Or maybe:

You need to look into the GROUP predicate.

select var1,
count(var1),
var2,
count(var1) + count(var2)
from tablename
group by var1,
var2

The error you received was because you were mixing a grouped value ( count ) and a non-grouped value ( id ).

You might just try adding the following:

select
v1.medicareid, (a1.datedischarged - a1.dateadmitted)*500 + count(v1.medicareid)*d1.visitfee
from
admission a1, doctor d1, visit v1
where
v1.medicareid = 'abcdefghik' and
a1.medicareid = v1.medicareid and
d1.eid = v1.doctorid
group by v1.medicareid;