Jim,

Try

SELECT fleetid, SUM(Gallons) AS Gallons, SUM(IIF(OnNetwork, Gallons, 0)) AS NetworkGallons
FROM atable
WHERE some condition
GROUP BY fleetid

>I have a table with two fields in particular that I am interested in for this example. The first is logical ('onnetwork'), the other is numeric (gallons). I am trying to construct a SQL query that will summarize the gallons quantity based on the "OnNetwork" flag for each "FleetId". Here is the syntax I thought would work:
>
>SELECT fleetid, SUM(Gallons) AS Gallons, IIF(OnNetwork, SUM(Gallons), 0) AS NetworkGallons
>FROM atable
>WHERE some condition
>GROUP BY fleetid
>
>I thought this would be the correct syntax and tally gallons for all records for the fleetid (Gallons) as well as the tally for gallons for records where the OnNetwork flag is .T.. The SUM() function in the IIF() statement is not giving me the results I would expect. It is evaluating the first instance of the OnNetwork flag for each instance of the "Fleet" field, and if OnNetwork = .T., I get a summation of all of the gallons for the fleet regardless of the value of OnNetwork for the other records. If the first instance of the OnNetwork flag is .F., then my result is zero for NetworkGallons even if there are some records where OnNetwork = .T.
>
>I can come up with other ways to construct multiple queries to get the result, but it sure seems like I should be able to get the syntax to give me the results I'm looking for in one query. I do not want to filter on OnNetwork in the WHERE clause because I also want to have a running total on all gallons for the fleetid regardless of the OnNetwork flag for each record.
>
>Any ideas?
>
>Thanks in advance.