Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
galkal
Starting Member
2 Posts |
 Posted - 2009-06-05 : 10:58:24
|
I have had a user submit a request for the following SQL query from SAP Business One to only return 1 row per order: SELECT T0.DocDueDate 'Due Date', T0.DocNum, T0.U_Status, T0.CardName, T0.NumAtCard, T0.Address2, T0.U_Route, T0.U_Driver, T0.U_PostCode, sum(T1.Weight1) 'Weight', T0.U_DelNotes, T2.TrnspName FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSHP T2 ON T0.TrnspCode = T2.TrnspCode WHERE T0.DocNum IN (54970, 54605, 53836) and T1.LineStatus = 'o' GROUP BY T0.DocDueDate, T0.DocNum, T0.U_Status, T0.CardName, T0.NumAtCard, T0.Address2, T0.U_Route, T0.U_Driver, T0.U_PostCode,T1.Weight1, T0.U_DelNotes, T2.TrnspNameCurrently, this returns 14 rows in total for the 3 orders stated in the WHERE command, how or what is needed to amend this query so that it only returns 3 rows in total (1 row per order) with a total weight showing in the sum field. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-06-05 : 11:02:39
|
First shot:Take T1.Weight1 away from GROUP BY.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
galkal
Starting Member
2 Posts |
Posted - 2009-06-05 : 11:08:40
|
| BINGO !simple really... I guess!Thanks! |
|
|
|
|
|
|
|
|
SQL 'SUM(Field1)