Group By with 2 outer joins

[rmotwani]

Hi, Everybody !

I have a small problem.

If I run a query with group by having two outer joins, there, group by is not working i suppose.

Case:
table 1 : Product master having product info
table 2 & 3 : receipt master and transaction having prod_id and qty
table 4 & 5 : despatch master and transaction having prod_id and qty

Requirement:
A Single query which fetches one record from master, multiple records from receipt and despatch because the transaction table is having multiple records for the same product

Query:
select m.prod_id, isnull(sum(R.RE CEIVED_QTY),0) receipt, isnull(sum(D.DE SPATCH_QTY),0) despatch
from product_master m
left outer join RECEIPT_TR r on m.prod_id=r.pro d_id
left outer join DESPATCH_TR d on m.prod_id=r.pro d_id
group by r.prod_id, d.prod_id

Problem:
If i have 2 rows in despatch, the ISSUED column doubles the sum of d.ISSUED,
If i have 3 rows, it give thrice the sum and so on.....

Pls Help Me !!

[nedu]

Hi,

Try this query. It may work as per u r requirement . .

[code=sql]
select m.prod_id, isnull(sum(R.RE CEIVED_QTY),0) receipt, isnull(sum(D.DE SPATCH_QTY),0) despatch
from product_master m
left outer join RECEIPT_TR r on m.prod_id=r.pro d_id
left outer join DESPATCH_TR d on m.prod_id=d.pro d_id
group by m.prod_id[/code]


Regards,
Nedu. M

[bonniesa]

Hello all

I too face similar problem but unable to find a solution .
Suppose in my query i am trying to fetch more than one fields then it forces me to includes all those fields in the group by clause too.

How come it seems to be working fine?

Can you provide a solution

Thanks
bonnie

Hi,

Try this query. It may work as per u r requirement . .

[code=sql]
select m.prod_id, isnull(sum(R.RE CEIVED_QTY),0) receipt, isnull(sum(D.DE SPATCH_QTY),0) despatch
from product_master m
left outer join RECEIPT_TR r on m.prod_id=r.pro d_id
left outer join DESPATCH_TR d on m.prod_id=d.pro d_id
group by m.prod_id[/code]


Regards,
Nedu. M

[Delerna]

Hello all

I too face similar problem but unable to find a solution .
Suppose in my query i am trying to fetch more than one fields then it forces me to includes all those fields in the group by clause too.

How come it seems to be working fine?

Can you provide a solution

Thanks
bonnie

Hi bonnie
There is no solution. These queries are called agregate queries and the simple fact is that every field selected in an aggregate query must be aggregated.
It dosn't make sense to aggregate only some fields and not others.
GROUP BY is an aggregate function so any field in your selection that you don't want to be SUM() or MIN() or MAX() or AVG() or any of the other aggregate functions must be put into the GROUP BY clause.
Thats just the way it is.