Hi,
 
I need to run a query that has several columns that calculate the sum of that column, but filtered down.

Example:

select
Stations.StationName as "Station Name",
sum(itembalance.balanceonhand * Itemmaster.standardcost) as 'station total value',
sum(txcost)
from  transactionhistory
join itemmaster on (itemmaster.itemnumber = transactionhistory.itemnumber)
join Itembalance on (ItemMaster.ItemID = ItemBalance.ItemID)
join Stations on (Stations.StationID = ItemBalance.StationID)
group by Stations.StationName

Now this works, but the second field needs to be filtered down as follows:

(select sum(txcost) from transactionhistory where txtype in (select txtype from transactionhistory where txtype='is') group by sourcestationname)

when I do this, i get an error "Subquery cannot return more than one row"

If i do not put the group by in the subquery, then i get the sum of all entries listed on every row. I need this to still be grouped by sourcestationname.

Any thoughts?

sorry if this is not explained very well...