You could use group by but remember that all columns in your select
statement must either by included in the Group By clause, or it must be
contained in an aggreage function, such as SUM() or AVG().

So, your query would look like this

Select PatientID, ROOMnumber, Name, SUM(Cost) from <table_name>

GROUP BY PatientID,RoomNumber,Name

This becomes a problem if for example you have more than one Patient in a
room, such as in a general ward in a hospital.

Hope this helps.

Regards,

Norman

_____

From: casibart [mailto:casibart@...]
Sent: 17 January 2005 12:56
To: firebird-support@yahoogroups.com
Subject: [firebird-support] General SQL question. What is the best way?




Hi,

I have a long running query that returns some patient names with the
tests and the prices like this.

PATIENT1, ROOM#, TEST NAME, COST
PATIENT2, ROOM#, TEST NAME, COST
PATIENT3, ROOM#, TEST NAME, COST
..


This is ok, but I also want to get the SUM of the COST FIELD. How can
I do it? Run the same long running query again with the "SUM"
function? Or is it better looping through the query results while
summing up the costs field? Or is it better using "group by" ?

Any ideas? Thank you.






_____

Yahoo! Groups Links

* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



[Non-text portions of this message have been removed]