Board index » delphi » SQL self join ??

SQL self join ??

I have a table with records movement from two places like

     Loc1  Loc2   ItemName   Qty

If an entry look like
     L1     L2       Apple          100

means there is 100 apples move from L1 to L2,
i.e. L1 has -100 apple and L2 has +100 apples
L1 may appear in Loc1 or Loc2

How do I perform an SQL to get the location balance???

Now I do as:

      Select  Loc1 as Location, ItemName, Sum(-qty) Bal
      from Mytable
      group by ItemName,Mytable.Loc1
   union all
      Select  Loc2 as Location, ItemName, Sum(qty) Bal
      from myTable
      group by ItemName,Mytable.Loc2
   order by ItemName

This returns each location with a +ve sum and a -ve sum, which is not
what I want.  I want a single entry for each ItemName.

Any expert to give some suggestoin how I can do it in a simpler way??

 

Re:SQL self join ??


Quote
Anders LEE wrote:

> I have a table with records movement from two places like

>      Loc1  Loc2   ItemName   Qty

> If an entry look like
>      L1     L2       Apple          100

> means there is 100 apples move from L1 to L2,
> i.e. L1 has -100 apple and L2 has +100 apples
> L1 may appear in Loc1 or Loc2

> How do I perform an SQL to get the location balance???

> Now I do as:

>       Select  Loc1 as Location, ItemName, Sum(-qty) Bal
>       from Mytable
>       group by ItemName,Mytable.Loc1
>    union all
>       Select  Loc2 as Location, ItemName, Sum(qty) Bal
>       from myTable
>       group by ItemName,Mytable.Loc2
>    order by ItemName

> This returns each location with a +ve sum and a -ve sum, which is not
> what I want.  I want a single entry for each ItemName.

> Any expert to give some suggestoin how I can do it in a simpler way??

Hi Anders,

Try this:

select distinct (sum(loc1) - sum(qty)) as bal1,
                (sum(loc2) + sum(qty)) as bal2,
                 sum(qty) as change
from Mytable

"distinct" is what you really need to show only the final summed up row.

Greetings,
Craig Manley.

Other Threads