You can group only by iTrading_Partner_ID and add SUM for all other column.
>I have this query:
>
>SELECT t.iTrading_Partner_ID,
> tp.sTP_Name1,
> tar.AR,
> tdp.Depo,
> tar.AR + tdp.Depo AS iNetAr,
> tp.decTP_Moneyline
> FROM Trade t
> JOIN Trading_Partner tp ON tp.iTrading_Partner_ID = t.iTrading_Partner_ID
> JOIN Product p ON p.sProduct_CD = t.sProduct_CD
> JOIN #TmpAR tar ON tar.iTrade_Id = t.iTrade_Id
> JOIN #TmpDepo tdp ON tdp.iTrade_Id = t.iTrade_Id
> WHERE (t.decTrade_Cash_Balance <> 0 OR
> t.decTrade_Product_Balance <> 0) AND
> (t.sTran_Group_CD <> 'MAR' AND
> t.sTran_Group_CD <> 'FUT' AND
> t.sTran_Group_CD <> 'PRO' AND
> t.sTran_Group_CD <> 'CON') AND
> t.iTrading_Partner_ID <> 999999 AND
> p.sCom_Type_CD <> 'F'
> GROUP BY t.iTrading_Partner_ID, tp.sTP_Name1, tar.AR, tdp.Depo, tp.decTP_Moneyline
> ORDER BY tp.sTP_Name1
>
>
>
>it returns this data:
>
>
>100022 4216.0000000000 0.00000000 4216.00000000 25000.00000
>100050 0.0000000000 0.00000000 0.00000000 25000.00000
>100073 0.0000000000 0.00000000 0.00000000 50000.00000
>100073 9615.0000000000 0.00000000 9615.00000000 50000.00000
>100116 0.0000000000 -36800.00000 -36800.00000 0.00000
>100116 0.0000000000 -4700.000000 -4700.000000 0.00000
>100116 0.0000000000 -3831.400000 -3831.400000 0.00000
>100116 0.0000000000 -1915.700000 -1915.700000 0.00000
>100116 0.0000000000 0.00000000 0.00000000 0.00000
>100116 7080.0000000000 0.00000000 7080.00000000 0.00000
>100116 7460.0000000000 0.00000000 7460.00000000 0.00000
>100133 -1700.0000000000 0.00000000 -1700.000000 300000.00000
>100133 0.0000000000 0.00000000 0.00000000 300000.00000
>100133 3950.0000000000 0.00000000 3950.00000000 300000.00000
>100133 7000.0000000000 0.00000000 7000.00000000 300000.00000
>100136 0.0000000000 0.00000000 0.00000000 5000000.00000
>100136 177.4100000000 0.00000000 177.41000000 5000000.00000
>100136 1300.3300000000 0.00000000 1300.33000000 5000000.00000
>100148 25.0000000000 0.00000000 25.00000000 0.00000
>100159 -217.7000000000 0.00000000 -217.7000000 0.00000
>100159 0.0000000000 0.00000000 0.00000000 0.00000
>100211 0.0000000000 0.00000000 0.00000000 0.00000
>100219 0.0000000000 -20304.00000 -20304.00000 75000.00000
>100219 0.0000000000 -8570.000000 -8570.000000 75000.00000
>
>
>Notice that the rows are not summed by the first column, the iTrading_Partner_ID. How do I get the data summed buy iTrading_Partner_ID?
If it's not broken, fix it until it is.
My Blog
