count

[zik]

I decided to resend this thread because I was struggling making my point clear.

I will like to see my output formatted like this:

t.lname #Unsigned t.id t.fname t.spec t.docid
Jim 91 13 eddy 15 30

This is what I have:
select distinct t.lname, sum(t.id) as '# Unsigned', t.fname, t.spec, t.docid
from t
where t.comp = 4
and t.rej = 2
group by t.lastname, t.id, t.firstname, t.specialty, t.docid
Please help!

 

[rcurva]

I don't know if I got you right, but give this a shot:

select t.lname, u.unsigned, t.id, t.fname, t.spec, t.docid
from table1 t,
(select y.lname, y.fname, y.spec, y.docid, sum(y.id) "Unsigned"
from table1 y
group by y.lname, y.fname, y.spec, y.docid) u
where t.lnmae = u.lname
and t.fname = u.fname
and t.spec = u.spec
and t.docid =u.docid
and t.comp = 4
and t.rej = 2;

Let me know if this helps.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[zik]

hi rcurva!
thanks for responding to my thread.
this sql statement is a cartesian join and is hanging up.
what i did was change my original sql and change sum(t.id) to count(t.id) and it worked.
thanks again