1

I'm not sure my question is asking the right question, so i will be as specific as possible in here.

I have the following data which i have changed the values of. Test data - current output

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.

Test output data

1
SELECT MAX(co.[slsman])               AS Salesman, 
       MAX(co.[cust_num])             AS "Customer Number", 
       MAX(ca.NAME)                   AS "Customer name", 
       co.[co_num]                    AS "Customer Order", 
       ci.item                        AS item, 
       MAX(ca.curr_code)              AS curr_code, 
       SUM(ci.price * ci.qty_ordered) AS sum_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' 
GROUP BY  co.co_num,
          ci.item
ORDER  BY Salesman, 
          "Customer Number"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.