sum expressions

This is untested, but may be a good starting point:

with count_openc as
 (select cr_count.cnt_openc
    from cr_count
   where cnt_date = '01-aug-13'
     and cnt_item = '20001'
     and cnt_str_id = '2001'),
count_close31 as
 (select cr_count.cnt_close
    from cr_count
   where cnt_date = '31-aug-13'
     and cnt_item = '20001'
     and cnt_str_id = '2001')
select cr_count.cnt_item,
       cr_track.ti_name_a,
       cr_uom.uom_code,
       (select cr_count.cnt_openc
          from cr_count
         where cnt_date = '01-aug-13'
           and cnt_item = '20001'
           and cnt_str_id = '2001') Open_Bal,
       sum(cr_count.cnt_trin) Tr_In,
       sum(cr_count.cnt_rcv) Recv,
       sum(cr_count.cnt_dsd) DSD,
       sum(cr_count.cnt_prod) Prod,
       sum(cr_count.cnt_void) Void,
       sum(cr_count.cnt_trout) Tr_Out,
       sum(cr_count.cnt_waste) Waste,
       sum(cr_count.cnt_usage) Usage,
       sum(cr_count.cnt_cons) Cons,
       sum(cr_count.cnt_sold) Sold,
       count_close31.cnt_close End_Bal,
       (count_openc.cnt_openc + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) Tot_In,
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold)) Tot_Out,
       (count_openc.cnt_openc + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) -
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold)) Comp_Close,
       count_close31.cnt_close -
       (((select cr_count.cnt_openc
            from cr_count
           where cnt_date = '01-aug-13'
             and cnt_item = '20001'
             and cnt_str_id = '2001') + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) -
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold))) Short_Over
  from count_openc,
       count_close31,
       cr_count,
       cr_track,
       cr_uom
 where ((cr_track.ti_id = cr_count.cnt_item) and (cr_uom.uom_id = cr_track.ti_puom))
   and cnt_date between '01-aug-13' and '31-aug-13'
   and cnt_item = '20001'
   and cnt_str_id = '2001'
 group by cr_count.cnt_item,
          cr_track.ti_name_a,
          cr_uom.uom_code;

Select allOpen in new window

I agree with above: using CTE is the best way to solve this.

error , not group by
 count_close31.cnt_close End_Bal,

but any how , this not solving my issue
cause i would like to add another more expressions
i dislike to repeat again the whole statement every time

i dislike to repeat again the whole statement every time

Then use "WITH", this is what CTE is for/does...

or clarify your problem a bit more detailed please

I'd like more information.
Q1. what is the data type of cr_count.cnt_date ?
Q2, how is it you can use strings like  '31-aug-13' as a reliable date?

There are several subqueries in the selection list, e.g.

      , (
                SELECT
                        cr_count.cnt_openc
                FROM cr_count
                WHERE cnt_date = '01-aug-13'
                        AND cnt_item = '20001'
                        AND cnt_str_id = '2001'
        )                                                                                                                                   
        Open_Bal

Select allOpen in new window

Q3. Will this (and the other similar subquey) return just a single value - every time?
          (They will fail if they return > 1 value)
Q4. Why is that subquery for just one date and not the whole of August?

Q5. Why not use ANSI join syntax? e.g.

FROM cr_count AS C
INNER JOIN cr_track AS T ON cr_count.cnt_item = cr_track.ti_id
INNER JOIN cr_uom   AS U ON cr_track.ti_puom = cr_uom.uom_id
WHERE (     c.cnt_date >= to_date('2013-08-01', 'YYYY-MM-DD')
        AND c.cnt_date  < to_date('2013-09-01', 'YYYY-MM-DD') ) 

Select allOpen in new window

and avoiding use 'between' is shown above also

>>but any how , this not solving my issue
>>i dislike to repeat again the whole statement every time
that is exactly what Alex has solved, do it once (in CTE) then re-use it.

CTE = Common Table Expression, Oracle often refers to it as "Sub Query Factorization", but they are the same thing. Most of us (it appers) use CTE
they are "named" queries, that are reusable in the main query.

>>i would like to add another more expressions
what are they?

{+edit for spelling, sorry}

ok  alex , let me try
but what about that error

error , not group by
at this statement

 count_close31.cnt_close End_Bal,



partuell
i would like to add :
having  short_over ( which is last expression in my first post )  > +1 or < -1

Unfortunately I cannot test this...

with count_openc as
 (select cr_count.cnt_openc
    from cr_count
   where cnt_date = '01-aug-13'
     and cnt_item = '20001'
     and cnt_str_id = '2001'),
count_close31 as
 (select cr_count.cnt_close
    from cr_count
   where cnt_date = '31-aug-13'
     and cnt_item = '20001'
     and cnt_str_id = '2001')
select cr_count.cnt_item,
       cr_track.ti_name_a,
       cr_uom.uom_code,
       max(count_openc.cnt_openc) Open_Bal,
       sum(cr_count.cnt_trin) Tr_In,
       sum(cr_count.cnt_rcv) Recv,
       sum(cr_count.cnt_dsd) DSD,
       sum(cr_count.cnt_prod) Prod,
       sum(cr_count.cnt_void) Void,
       sum(cr_count.cnt_trout) Tr_Out,
       sum(cr_count.cnt_waste) Waste,
       sum(cr_count.cnt_usage) Usage,
       sum(cr_count.cnt_cons) Cons,
       sum(cr_count.cnt_sold) Sold,
       max(count_close31.cnt_close) End_Bal,
       (max(count_openc.cnt_openc) + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) Tot_In,
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold)) Tot_Out,
       (max(count_openc.cnt_openc) + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
       sum(cr_count.cnt_void)) -
       (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold)) Comp_Close,
       max(count_close31.cnt_close) -
       ((max(count_openc.cnt_openc) + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
        sum(cr_count.cnt_void)) -
        (sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) + sum(cr_count.cnt_sold))) Short_Over
  from count_openc,
       count_close31,
       cr_count,
       cr_track,
       cr_uom
 where ((cr_track.ti_id = cr_count.cnt_item) and (cr_uom.uom_id = cr_track.ti_puom))
   and cnt_date between '01-aug-13' and '31-aug-13'
   and cnt_item = '20001'
   and cnt_str_id = '2001'
 group by cr_count.cnt_item,
          cr_track.ti_name_a,
          cr_uom.uom_code
having ((count_openc.cnt_openc + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
          sum(cr_count.cnt_void)) -(sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) +
                             sum(cr_count.cnt_sold))) not in(-1, 0, 1);

Select allOpen in new window

again same error at this line

having ((count_openc.cnt_openc + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
          sum(cr_count.cnt_void)) -(sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) +
                             sum(cr_count.cnt_sold))) not in(-1, 0, 1);

Select allOpen in new window

having ((max(count_openc.cnt_openc) + sum(cr_count.cnt_trin) + sum(cr_count.cnt_rcv) + sum(cr_count.cnt_dsd) + sum(cr_count.cnt_prod) +
          sum(cr_count.cnt_void)) -(sum(cr_count.cnt_trout) + sum(cr_count.cnt_waste) + sum(cr_count.cnt_usage) + sum(cr_count.cnt_cons) +
                             sum(cr_count.cnt_sold))) not in(-1, 0, 1);
                                           

yes it works

it is ok now , but let me wait for partuel if he has a shorter solution


Q1. what is the data type of cr_count.cnt_date ?
         Date
Q2, how is it you can use strings like  '31-aug-13' as a reliable date?
       each one will bring calculated balance at several date
Q3. Will this (and the other similar subquey) return just a single value - every time?
          (They will fail if they return > 1 value)
        yes , it should
Q4. Why is that subquery for just one date and not the whole of August?
      the transaction will be for the whole month of august , but the specific formula of beg    & end balance will bring the specific data

Q5. Why not use ANSI join syntax? e.g.
     it could be , why not

NiceMan321, you are too nice to me.
No need to wait, Alex140181 is providing great advice.

Using the CTE is the best advice I could give (as it allows 'do it once' and 're-use')

i.e. I believe you have the answer already.

-------------- No points please ------------
a2. I strongly suggest you use YYYY-MM-DD instead of YY-Mon-DD
       OR, use the to_date() function
             to_date('31-aug-13','DD-MON-YY')

a3. "should" isn't strong enough. Force it to return just one value with rownum (line 8)

      , (
                SELECT
                        cr_count.cnt_openc
                FROM cr_count
                WHERE cnt_date = '01-aug-13'
                        AND cnt_item = '20001'
                        AND cnt_str_id = '2001'
                        AND rownum = 1
        )   as  Open_Bal

Select allOpen in new window

A5. please do use ANSI syntax, once you get used to it you will prefer it

wellcome back my best freind
you often provides more than what i ask

a2. I strongly suggest you use YYYY-MM-DD instead of YY-Mon-DD

i really love to use it , but  sql developer dosent allow me to use else of YY-MMM-DD , i don't know how to change its setting

A5. please do use ANSI syntax, once you get used to it you will prefer it

what is the different between this :

FROM cr_count AS C
INNER JOIN cr_track AS T ON cr_count.cnt_item = cr_track.ti_id
INNER JOIN cr_uom   AS U ON cr_track.ti_puom = cr_uom.uom_id
WHERE (     c.cnt_date >= to_date('2013-08-01', 'YYYY-MM-DD')
        AND c.cnt_date  < to_date('2013-09-01', 'YYYY-MM-DD') ) 

Select allOpen in new window

and this :

FROM cr_count, cr_track, cr_uom
WHERE (    (cr_track.ti_id = cr_count.cnt_item)
        AND (cr_uom.uom_id = cr_track.ti_puom)
      ) 

Select allOpen in new window

-- before
FROM cr_count, cr_track, cr_uom

-- after
FROM cr_count AS C
INNER JOIN cr_track AS T ON cr_count.cnt_item = cr_track.ti_id
INNER JOIN cr_uom   AS U ON cr_track.ti_puom = cr_uom.uom_id

Select allOpen in new window

The syntax difference is that the joining logic is contained within the FROM clause  (and the FROM clause is evaluated before the WHERE clause).

To me, the real difference/advantage is:
a. The WHERE clause becomes easier to understand
b. as more people become mostly familiar with ANSI syntax, the code will be easier to maintain in the longer term
c. ANSI syntax is far easier to use for advanced join conditions
d. it is almost impossible for "miss a join" using ANSI syntax, but it is easy to do in older syntax

Compare these 2 where clauses:

-- before
 where ((cr_track.ti_id = cr_count.cnt_item) and (cr_uom.uom_id = cr_track.ti_puom))
   and cnt_date between '01-aug-13' and '31-aug-13'
   and cnt_item = '20001'
   and cnt_str_id = '2001'
  

-- after
 where cnt_date between '01-aug-13' and '31-aug-13'
   and cnt_item = '20001'
   and cnt_str_id = '2001'
 

Select allOpen in new window

thanx for the explanation

FROM cr_count AS C
INNER JOIN cr_track AS T ON cr_count.cnt_item = cr_track.ti_id
INNER JOIN cr_uom   AS U ON cr_track.ti_puom = cr_uom.uom_id

Select allOpen in new window

i could use it like this :

FROM cr_count AS C
INNER JOIN cr_track AS T ON c.cnt_item = t.ti_id
INNER JOIN cr_uom   AS U ON t.ti_puom = u.uom_id

Select allOpen in new window

remaining one thing

a2. I strongly suggest you use YYYY-MM-DD instead of YY-Mon-DD
i really love to use it , but  sql developer dosent allow me to use else of YY-MMM-DD , i don't know how to change its setting

do u know how to change date format in sql developer

before going too far, please note we are "way off-topic" and that your question was handled by Alex. If the question has been answered it should be closed accordingly.

The other topics I have raised are purely advice/notes that you don't have to adopt.

You should be able to use 'YYYYMMDD' in your sql queries without changing date defaults and I'd rather not go too far into this here. I suggest you read this first:
The ultimate guide to the datetime datatypes (Tibor Karaszi)

the solution of alex is correct , but still i'm looking for an answer :
is there any way to use the alias , or i should repeat it
for example :
select  sum(debit) deb , sum(credit) cred
from my_table;

if i would like to have the difference between deb & cred  ,
is there any way to use it like this :

select  sum(debit) deb , sum(credit) cred , (deb-cred) Diff
from my_table;

instead of say :

select  sum(debit) deb , sum(credit) cred , (sum(debit) - sum(credit)) Diff
from my_table;

well, you can "nest" a substantial part of the query perhaps, then reference the summed values by their aliases - like this - but it's totally untested

WITH
        count_openc
        AS (
                        SELECT
                                cr_count.cnt_openc
                        FROM cr_count
                        WHERE cnt_date = '01-aug-13'
                                AND cnt_item = '20001'
                                AND cnt_str_id = '2001'
                                and rownum = 1
                )
,
        count_close31
        AS (
                        SELECT
                                cr_count.cnt_close
                        FROM cr_count
                        WHERE cnt_date = '31-aug-13'
                                AND cnt_item = '20001'
                                AND cnt_str_id = '2001'
                                and rownum = 1
                )
SELECT
          cc.cr_count.cnt_item
        , cc.cr_track.ti_name_a
        , cc.cr_uom.uom_code
        , cc.Tr_In
        , cc.Recv
        , cc.DSD
        , cc.Prod
        , cc.Void
        , cc.Tr_Out
        , cc.Waste
        , cc.Usage
        , cc.Cons
        , cc.Sold
        , (count_openc.cnt_openc)+ cc.Tr_In + cc.Recv 
          + cc.DSD + cc.Prod + cc.Void)                        AS Tot_In
        , (cc.Tr_Out + cc.Waste + cc.Usage
          + cc.Cons + cc.Sold)                                 AS Tot_Out
        , (count_openc.cnt_openc)+ cc.Tr_In + cc.Recv
          + cc.DSD + cc.Prod + cc.Void)
          - (cc.Tr_Out + cc.Waste + cc.Usage
           + cc.Cons + cc.Sold)                              AS Comp_Close
        , count_close31.cnt_close
          -  ((count_openc.cnt_openc + cc.Tr_In + cc.Recv
               + cc.DSD + cc.Prod + cc.Void)
          -  (cc.Tr_Out + cc.Waste + cc.Usage
            + cc.Cons + cc.Sold))                            AS Short_Over
FROM (
                SELECT
                        cr_count.cnt_item
                      , cr_track.ti_name_a
                      , cr_uom.uom_code
                      , SUM(cr_count.cnt_trin)    AS Tr_In
                      , SUM(cr_count.cnt_rcv)     AS Recv
                      , SUM(cr_count.cnt_dsd)     AS DSD
                      , SUM(cr_count.cnt_prod)    AS Prod
                      , SUM(cr_count.cnt_void)    AS Void
                      , SUM(cr_count.cnt_trout)   AS Tr_Out
                      , SUM(cr_count.cnt_waste)   AS Waste
                      , SUM(cr_count.cnt_usage)   AS Usage
                      , SUM(cr_count.cnt_cons)    AS Cons
                      , SUM(cr_count.cnt_sold)    AS Sold
                FROM cr_count
                INNER JOIN cr_track ON cr_track.ti_id = cr_count.cnt_item
                INNER JOIN cr_uom ON cr_uom.uom_id = cr_track.ti_puom
                WHERE cnt_date BETWEEN '01-aug-13' AND '31-aug-13'
                        AND cnt_item = '20001'
                        AND cnt_str_id = '2001'
                GROUP BY
                         cr_count.cnt_item
                       , cr_track.ti_name_a
                       , cr_uom.uom_code
                HAVING ((count_openc.cnt_openc + SUM(cr_count.cnt_trin) + SUM(cr_count.cnt_rcv) + SUM(cr_count.cnt_dsd) + SUM(cr_count.cnt_prod) +
                SUM(cr_count.cnt_void)) - (SUM(cr_count.cnt_trout) + SUM(cr_count.cnt_waste) + SUM(cr_count.cnt_usage) + SUM(cr_count.cnt_cons) +
                SUM(cr_count.cnt_sold))) NOT IN (-1, 0, 1)
    ) CC
CROSS JOIN count_openc   /* MAX OF 1 RECORD */
CROSS JOIN count_close31 /* MAX OF 1 RECORD */

Select allOpen in new window

it gives error at line 37
FROM keyword not found

line 37
line 41

change
 , (count_openc.cnt_openc)+

to

 , (count_openc.cnt_openc +

i.e. remove 2x ")" from the query

re-try

error line 75

count_openc.cnt_openc

invalid identifier

ok dear
thanx any how
i will do to reconsile it
thanx

Nice work, Paul. Thought the points would have been distributed amongst us...

i'm so sorry alex
it is my mistake
how do i repair it ?

No need to repair anything.  But keep that in mind for the next time. ..

i'm so sorry
i really confused ,because the question solved time ago , i just noticed that it still open , i review who answer it by quickly , i found the last post of paul
ii apologize again
i hope you answer my next questions , thanx