Do you really need the extra table?

You can get that data you need with a simple query, which you can obviously create as a view if you want it to appear like a table.

This will get you the data you are looking for:

select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total
from t
/

This will create a view to show you the data as if it were a table:

create or replace view t2
as
select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total 
from t
/

If you really need the table, do you mean that you need it constantly updated? or just a one off? Obviously if it's a one off you can just "create table as select" using the above query.

Test data I used is:

create table t(account number, bookdate date, amount number);

insert into t(account, bookdate, amount) values (1, to_date('20080101', 'yyyymmdd'), 100);

insert into t(account, bookdate, amount) values (1, to_date('20080102', 'yyyymmdd'), 101);

insert into t(account, bookdate, amount) values (1, to_date('20080103', 'yyyymmdd'), -200);

insert into t(account, bookdate, amount) values (2, to_date('20080102', 'yyyymmdd'), 200);

commit;

edit:

forgot to add; you specified that you wanted the table to be ordered - this doesn't really make sense, and makes me think that you really mean that you wanted the query/view - ordering is a result of the query you execute, not something that's inherant in the table (ignoring Index Organised Tables and the like).

Check the query for the third problem:

select *, 
    sum(Price) over (partition by Category, Unit order by SerialNo) as RunningTotal,
    case when lead(SerialNo) over (partition by Category, Unit order by SerialNo) is null 
    then cast(sum(Price) over (partition by Category, Unit) as varchar(max)) else '' end as TotalValue 
from MyTable
order by Category, Unit, SerialNo

Ah, I think I have figured it out.

select a.*, sum(Amount) over (partition by Location, Product order by Date) as Running_Amt
from Example_Table a

This is finally simple to do in SQL Server 2012, where SUM and COUNT support OVER clauses that contain ORDER BY. Using Cris's #Checks table definition:

SELECT
  CompanyID,
  count(*) over (
    partition by CompanyID
    order by Cleared, ID
  ) as cnt,
  str(100.0*sum(Amount) over (
    partition by CompanyID
    order by Cleared, ID
  )/
  sum(Amount) over (
    partition by CompanyID
  ),5,1)+'%' as RunningTotalForThisCompany
FROM #Checks;

SQL Fiddle here.

Looking this over, due to the need for a distinct count and the lack of other options in Sybase ASE, you'll have to use a self-join:

SELECT
  Tr.InvoiceDt
 ,Tr.ChargeDt
 ,COUNT(DISTINCT PriorTr.TransactionID) AS TransactionCount
FROM
  mytable1 Tr
INNER JOIN
  mytable1 PriorTr
    ON PriorTr.InvoiceDt = Tr.InvoiceDt
        AND PriorTr.ChargeDt <= Tr.ChargeDt
GROUP BY
  Tr.InvoiceDt
 ,Tr.ChargeDt

Fiddle here (have to settle for the distant cousin, SQL Server): http://sqlfiddle.com/#!18/ff8e0d/2

Answer based on clarifications in comments

The question, really, is giving two data scenarios and saying in the first case, all rows should be returned in a select because when you order the rows by lineID and keep track of the cumulative sum of the Amount value then any time the BusinessUnit value changes if the cumulative sum of the Amount value is not zero, return the rows. In the second scenario, the cumulative sum for Amount is zero every time the Business Unit value changes when ordered by LineID - so no row is returned.

There are two ways to do this.

You can write a cursor that will iterate through your rows one by one and use local variables to store values, track the cumulative sum and note which rows fail the check, then somehow store those rows in a temporary table and return the table contents at the end.

However, as someone noted in comments, the strength in a database lies in set operations - dealing with sets of data at a time. So...

The second way to do this is as a "single" statement. This is really an aggregation of multiple statements - but they are all selects. The approach below makes use of the T-SQL lag function, which can read values from prior rows in an ordered result set. This function requires a partition clause - which allows us to create "windows" on our data - but we don't actually need those windows - we are happy to treat the whole dataset as a single window. So I guess ultimately this also processes rows one by one, but using T-SQL's native function rather than writing our own cursor.

Last note before the solution - you have a column [Value] which contains the word "Apple" on every row. It seems irrelevant to the question, so I have ignored it. If this column affects the behviour you are seeking, you will have to adjust the below SQL suitably to deal with your [Value] column.

Here is the solution - including data setup and tear down for each of your two scenarios.

Important! - The lineID value for the failing rows is in the column priorLineID (and not lineID)

This is because we are "reading behind" with the lag function - so we don't know if the businessUnit has changed until we get to the next row and look back at the prior one. At that time we know the businessUnit changed so we test whether the prior cumulative total was zero and if not, return the current row and provide the priorLineID in its own column. You can expand the SQL to return whatever prior row data values you need.

delete from test;
go

-- TEST CASE 1 - all rows returned because the cumulative total for Amount is not zero 
--  when BusinessUnit changes, when ordered by LineID 

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'DEF', 'Apple', 40.00 UNION
SELECT 3, 'ABC', 'Apple', -20.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00 

select * from test;

with EXPANDED_DATA as (
select lineID, BusinessUnit, value, amount from test 
union select 999999999,'','',0
),
PARTITIONED_DATA as (
select 
lineID,
BusinessUnit,
AMount,
lag(BusinessUnit,1,'') over (partition by 1 order by lineID) as priorBusinessUnit,
lag(lineID) over (partition by 1 order by lineID) as priorLineID,
lag(amount) over (partition by 1 order by lineID) as priorAmount
 from EXPANDED_DATA
)
,
WITH_PRIOR_CUMULATIVE_AMOUNT as (
select *,
case when priorBusinessUnit = businessUnit then priorAmount + amount else amount end as CumulativeBusinessUnitTotal
from PARTITIONED_DATA
),
WITH_PRIOR_TOTALS as (
select *, 
lag(CumulativeBusinessUnitTotal) over (partition by 1 order by lineID) as priorCumulativeTotal
 from WITH_PRIOR_CUMULATIVE_AMOUNT 
)
select * from WITH_PRIOR_TOTALS
where BusinessUnit <> priorBusinessUnit and priorCumulativeTotal <> 0



delete from test;
go

-- TEST CASE 2 - no rows returned because the cumulative total for Amount is zero 
--  when BusinessUnit changes, when ordered by LineID

INSERT INTO dbo.Test
SELECT 1, 'ABC', 'Apple', 20.00 UNION
SELECT 2, 'ABC', 'Apple', -20.00 UNION
SELECT 3, 'DEF', 'Apple', 40.00 UNION
SELECT 4, 'DEF', 'Apple', -40.00 

select * from test;

with EXPANDED_DATA as (
select lineID, BusinessUnit, value, amount from test 
union select 999999999,'','',0
),
PARTITIONED_DATA as (
select 
lineID,
BusinessUnit,
AMount,
lag(BusinessUnit,1,'') over (partition by 1 order by lineID) as priorBusinessUnit,
lag(lineID) over (partition by 1 order by lineID) as priorLineID,
lag(amount) over (partition by 1 order by lineID) as priorAmount
 from EXPANDED_DATA

)
,
WITH_PRIOR_CUMULATIVE_AMOUNT as (
select *,
case when priorBusinessUnit = businessUnit then priorAmount + amount else amount end as CumulativeBusinessUnitTotal
from PARTITIONED_DATA
),
WITH_PRIOR_TOTALS as (
select *, 
lag(CumulativeBusinessUnitTotal) over (partition by 1 order by lineID) as priorCumulativeTotal
 from WITH_PRIOR_CUMULATIVE_AMOUNT 
)
select * from WITH_PRIOR_TOTALS
where BusinessUnit <> priorBusinessUnit and priorCumulativeTotal <> 0

Original answer below

(Originally the question seemed to be about validating the insert statements at the time of insertion and I basically made the comment that you really can't do that. I will leave this part of the answer below).

If I understand your question correctly, you are saying the first attempt at insert is invalid because of the sequence of the select statements fails a business rule, and that the second attempt at insert is valid because the sequence of select statements passes that business rule. Is this correct?

If so, note that your select statements have been "joined together" (effectively), using union statements. This means your selection of four rows of data is carried out as a single statement - and there are no guarantees about the order in which those select statements will be processed.

The only difference between your first and second insert - as far as the database is concerned - is that the lineID value varies between the two statements, for a given combination of data.

However the more important implication to you is that it seems you want the database to validate something that it is not designed to validate - re-read my comment that there is no real difference between those two inserts, as far as the database is concerned.

Reading between the lines, I am wondering if your list of select statements is being generated by application code? If so, I would suggest your application should be validating the values it is appending to the query. That said, regardless of the order of rows, the net result (as far as the database is concerned) will be the same (notwithstanding the lineID difference).

You can use aggregation with additional logic:

select cs.*,
       (call_volume * 1.0 / (put_volume + call_volume)) as CallsContractPercentage,
       (put_volume * 1.0 / (put_volume + call_volume)) as PutContractPercentage,
       (put_volume + call_volume) as total_count
from (select symbol,
             sum(case when call_put = 'Call' then volume end) as call_volume,
             sum(case when call_put = 'Call' then premium end) as call_premium,
             sum(case when call_put = 'Put' then volume end) as put_volume,
             sum(case when call_put = 'Put' then premium end) as put_premium
      from options_flow of2
      where symbol ='PFE'
      group by created_date, symbol
     ) cs;