Ahmedabad	34860	20516	80720.063931	619	329   6300.63
Central Gujarat	2200	1200	815.8853765	24	24	2064
South Gujarat	100	80	99.16666	NULL	NULL	NULL


but  right answer is 

Ahmedabad	34860	20516	80720.063931	619	329    6300.63
Central Gujarat	2200	1200	815.8853765	12	12	1032
South Gujarat	100	80	99.16666	NULL	NULL	NULL

quote:

---

Originally posted by boyzs

Dear Friends,
I am new to to sql server. I have three different tables
1.TransationMaster
code pk,
TerritoryName,
TerritoryId,
2.SchemeTransationMaster
schemeTransationCode pk
TransationCode fk(Code TransationMaster table)
QtySecoundarySales
QtySchemeSales
AmoutforSecVolume
3.CashDiscountTransation
CashDiscCode pk
TransationCode fk(code TrasationMaster table)
SecQtyCashDisc
CashDiscQty
AmountOfCashDisc

i need sum from SchemeTransationMaster and CashDiscountTransation

my query is as follow


select ttm.TerritoryName as TerritoryName,
Sum(sttm.QtySecSales) as QtySecSales,
sum(sttm.QtySchemeSales) as QtySchemeSales,
sum(sttm.AmountOfSchemeVolume) as AmountOfSchemeVolume,
sum(cdtt.SecQtyOfCashDisc) as SecQtyOfCashDisc ,
sum(cdtt.QtyOfCashDisc) as QtyOfCashDisc,
sum(cdtt.TotalAmtOfCashDisc) as QtyOfCashDisc
from(select tm.TerritoryName as TerritoryName,
tm.Code as Code
from TransationMaster tm
where tm.IsDeleted = 0
)as ttm
Left Outer join
(select
stm.TransationCode as TransationCode,
stm.QtySecSales as QtySecSales,
stm.QtySchemeSales as QtySchemeSales,
stm.AmountOfSchemeVolume as AmountOfSchemeVolume
from SchemeTransationMaster stm
where stm.IsDeleted = 0

)as sttm
on ttm.Code=sttm.TransationCode
Left Outer Join
(
select DISTINCT cdt.TransationCode as TransationCode,
cdt.SecQtyOfCashDisc as SecQtyOfCashDisc,
cdt.QtyOfCashDisc as QtyOfCashDisc,
cdt.TotalAmtOfCashDisc as TotalAmtOfCashDisc
from CashDiscountTransation cdt
where cdt.IsDeleted = 0
)as cdtt
on ttm.Code = cdtt.TransationCode
group by ttm.TerritoryName


but gives me wrong data.. can any one please help me regards this.

Thank You

---

as I said the tables are
TransationMaster
   Code (Primary Key)(guid)
   TerritoryName (Varchar(max))
SchemeTransationMaster
    TransationCode (Fk---> pk to TransationMaster.Code )
    QtySecSales(float)
    QtySchemeSales(float)
    AmountOfSchemeVolume(float)
CashDiscountTransation 
   TransationCode (Fk---> pk to TransationMaster.Code )
   SecQtyOfCashDisc (float)
   QtyOfCashDisc (float)
   TotalAmtOfCashDisc(float)

  In table TransationMaster datas are as follows

TerritoryName  Code
Ahmd           1
Ahmd           2
Central Guj    3
Ahmd           4
South guj      5

  in table SchemeTransationMaster

TransationCode  QtySecSales QtySchemeSales AmountOfSchemeVolume 
  1              100          80              200
  1              50           5               100
  3              100          50              200
  5              100          50              100

in table CashDiscountTransation 
TransationCode SecQtyOfCashDisc QtyOfCashDisc TotalAmtOfCashDisc
  1              100              50            100
  4              50               10             20
  3              100              100           200
 

this are the data now i want output as
TerrName  QtySecSales QSchS   Amt     SecQtyC  QtyOfC   Amt
Ahmd          150      85     300       200     150     300
Central Guj   100      50     200       100     100     200
South guj     100      50     100       Null    Null    Null


Hopes now you get the my problem...


Query I use is nearer to my answer  but does not get the correct answer...

select ttm.TerritoryName as TerritoryName,
       Sum(sttm.QtySecSales) as QtySecSales,
       sum(sttm.QtySchemeSales) as QtySchemeSales,
       sum(sttm.AmountOfSchemeVolume) as AmountOfSchemeVolume,
       sum(cdtt.SecQtyOfCashDisc) as SecQtyOfCashDisc ,
       sum(cdtt.QtyOfCashDisc) as QtyOfCashDisc,
       sum(cdtt.TotalAmtOfCashDisc) as QtyOfCashDisc
from(select tm.TerritoryName as TerritoryName,
            tm.Code as Code
            from TransationMaster tm
            where tm.IsDeleted = 0
       )as ttm
Left Outer join
(select
       stm.TransationCode as TransationCode, 
       stm.QtySecSales as QtySecSales,
       stm.QtySchemeSales as QtySchemeSales,
       stm.AmountOfSchemeVolume as AmountOfSchemeVolume
       from SchemeTransationMaster stm
       where stm.IsDeleted = 0 )as sttm
on ttm.Code=sttm.TransationCode
Left Outer Join
(
   select 
       cdt.TransationCode as TransationCode,
       cdt.SecQtyOfCashDisc as SecQtyOfCashDisc,
       cdt.QtyOfCashDisc as QtyOfCashDisc,
       cdt.TotalAmtOfCashDisc as TotalAmtOfCashDisc
       from CashDiscountTransation cdt
       where cdt.IsDeleted = 0
)as cdtt
on ttm.Code = cdtt.TransationCode
group by ttm.TerritoryName

 

SELECT T.TerritoryName
    ,D1.*
    ,D2.*
FROM TransationMaster T
    LEFT JOIN
    (
        SELECT
            S.TransationCode
            ,SUM(S.QtySecSales) AS QtySecSales
            ,SUM(S.QtySchemeSales) AS QtySchemeSales
            ,SUM(S.AmountOfSchemeVolume) AS AmountOfSchemeVolume
        FROM SchemeTransationMaster S
        WHERE S.IsDeleted = 0 
        GROUP BY S.TransationCode
    ) D1
        ON T.Code = D1.TransationCode
    LEFT JOIN
    (
        SELECT C.TransationCode
            ,SUM(C.SecQtyOfCashDisc) AS SecQtyOfCashDisc
            ,SUM(C.QtyOfCashDisc) AS QtyOfCashDisc
            ,SUM(C.TotalAmtOfCashDisc) AS TotalAmtOfCashDisc
        FROM CashDiscountTransation C
        WHERE C.IsDeleted = 0
        GROUP BY C.TransationCode
    ) D2
        ON T.Code = D2.TransationCode
WHERE T.IsDeleted = 0