>But the query requires that I group by all the fields in the SELECT portion
not if you have aggregate functions;
>>>SELECT t.iTrading_Partner_ID,
>>> tp.sTP_Name1,
>>> sum(tar.AR),
>>> sum(tdp.Depo),
>>> sum(tar.AR + tdp.Depo AS iNetAr),
>>> sum(tp.decTP_Moneyline)
>>> FROM Trade t
...
>>> GROUP BY t.iTrading_Partner_ID, tp.sTP_Name1
>
>
>>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?
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com
