I'm not sure my question is asking the right question, so i will be as specific as possible in here.
Below is the code I am using to pull this data through.
/*this is used for the open order book*/ SELECT co.[slsman] AS Salesman, co.[cust_num] AS "Customer Number", ca.NAME AS "Customer name", co.[co_num] AS "Customer Order", ci.item, ca.curr_code, ci.price * ci.qty_ordered AS value FROM [co] co LEFT JOIN custaddr ca ON co.cust_num = ca.cust_num AND co.cust_seq = ca.cust_seq LEFT JOIN coitem ci ON co.co_num = ci.co_num WHERE co.co_num LIKE 's%' AND co.stat = 'o' AND co.cust_seq = '1' AND item NOT LIKE '%carriage%' AND ci.stat NOT LIKE 'f' AND ci.stat NOT LIKE 'c' ORDER BY co.slsman, co.cust_num
What I am trying to do is merge all rows where the Customer order and the item part number are the same, and sum all the costs of these rows. I have tried adding a group by before the order by, but i understand after some reading that this does not work how i thought it did.
GROUP BY co.co_num, ci.item
which gives the following error
Msg 8120, Level 16, State 1, Line 3 Column 'co.slsman' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
Below is what i want to be outputting. As you can see, i don't want to merge any cell text values, just the value one where customer order and item are the same.