Hi!, i have to run this on multiple table. Here i am writing example of 3 tables. selection criteria for OrdNo can be a range, also OrdNo is unique in TabHead1

TabHead1
OrdNo customer
100 cust1
101 cust1
101 cust2

TabDet1
OrdNo upc Qty
100 upc1 100
100 upc2 110
101 upc1 200

TabDet2
OrdNo upc cart Qty
100 upc1 cart1 10
100 upc1 cart2 11
100 upc1 cart3 15
100 upc2 cart4 5


Result expected for Summary version
OrdNo customer Qty sh_qty
100 cust1 210 41

Query1

Select TabHead1.OrdNo , TabHead1.customer ,
sum(TabDet1.Qty) Qty, Sum(TabDet2.Qty) sh_qty from TabHead1
Left join TabDet1 on TabDet1.OrdNo = TabHead1.OrdNo
Left join TabDet2 on TabDet2.OrdNo = TabDet1.OrdNo and TabDet1.upc = TabDet2.upc group by TabHead1.OrdNo , TabHead1.customer where TabHead1.OrdNo = ‘100’

Or Query2

Select TabHead1.OrdNo , TabHead1.customer , sum(TabDet1.Qty) Qty,
(Select Sum(TabDet2.Qty) sh_qty from TabDet2 where
TabDet2. OrdNo = TabDet1.OrdNo and TabDet1.upc = TabDet2.upc)
from from TabHead1
Left join TabDet1 on TabDet1.OrdNo = TabHead1.OrdNo
Group by TabHead1.OrdNo , TabHead1.customer where TabHead1.OrdNo = ‘100’

Or Query3
Select TabHead1.OrdNo , TabHead1.customer ,
(select sum(TabDet1.Qty) Qty from TabDet1 Where TabDet1.OrdNo = TabHead1.OrdNo ),
(Select Sum(TabDet2.Qty) sh_qty from TabDet2 where TabDet2.OrdNo = TabDet1.OrdNo)
from TabHead1
Group by TabHead1.OrdNo , TabHead1.customer where TabHead1.OrdNo = ‘100’

Which query (1,2,3) is better or is there a better way. In detailed version i have to group by OrdNo, customer, upc. Thanks in advance

Result expected for Detail version
OrdNo customer upc Qty sh_qty
100 cust1 upc1 100 36
100 cust1 upc2 110 5

Try to generate the Visual Explain plan for each of these queries and you'll have a fair enough idea on the best query to be used

Thank you nick, one more question :-- is there a function to concate a column during grouping e.g
Mytable
SNo col1 qty
A00 A1 10
A00 A2 20
A00 A3 30
A01 A1 5
A01 A2 1

Result expected:

A00 A1,A2,A3 60
A01 A1,A2,A3 6


Select SNo, Somefunction(col1), Sum(qty) from Mytable group by SNo

That's an FAQ. Search for string concatenation. You can use XMLAGG or a recursive query for that.