SQL Server - GROUP BY on one column

Question

I'm trying to get orders from an orderview. In my view I do have some rows with exactly the same values, but I want to group these values on orderid and take the sum of the quantity of that order.

My view results something like:

 Order_id   Customer_id   Article_id   Delivery_date   Quantity
 ---------------------------------------------------------------------------
 PR10.001   11            20.001a      17-04-2013      1
 PR10.001   11            20.001a      17-04-2013      1
 PR10.001   11            20.001a      17-04-2013      1
 PR13.001   15            41.022b      19-04-2013      1
 PR13.001   15            41.022b      19-04-2013      1

I want to do something like:

SELECT Order_id, Customer_id Article_id, Delivery_date, sum(Quantity)
FROM Orders
GROUP BY Order_id

To get something like:

 Order_id   Customer_id   Article_id   Delivery_date   Quantity
 ---------------------------------------------------------------------------
 PR10.001   11            20.001a      17-04-2013      3
 PR13.001   15            41.022b      19-04-2013      2

But I know grouping by one single column is not possible, otherwise you'll get the message:

[...] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there another possibility or workaround to group by one specific column in SQL Server?

Answer 1

You could use a CTE with SUM(Quantity)OVER(PARTITION BY Order_id) + ROW_NUMBER to pick out the desired row from the order-group:

WITH cte 
     AS (SELECT order_id, 
                customer_id, 
                article_id, 
                delivery_date, 
                quantity=Sum(quantity) 
                           OVER( 
                             partition BY order_id), 
                rn = Row_number() 
                       OVER( 
                         partition BY order_id 
                         ORDER BY delivery_date ASC) 
         FROM   orders) 
SELECT order_id, 
       customer_id, 
       article_id, 
       delivery_date, 
       quantity 
FROM   cte 
WHERE  rn = 1 

DEMO

However, your desired result seems to be incorrect (question edited)

This is my result:

ORDER_ID    CUSTOMER_ID ARTICLE_ID  DELIVERY_DATE   QUANTITY
PR10.001    11          20.001a         17-04-2013  3
PR13.001    15          41.022b         19-04-2013  2