SQL Server - GROUP BY on one column
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
Assuming the other columns are functionally dependent on the grouping column, the simplest answers are either
a. Group by the other columns as well:
SELECT Order_id, Customer_id, Article_id, Delivery_date, sum(Quantity)
FROM Orders
GROUP BY Order_id, Customer_id, Article_id, Delivery_date
or
b. Use an aggregate function such as max
:
SELECT Order_id, max(Customer_id), max(Article_id), max(Delivery_date), sum(Quantity)
FROM Orders
GROUP BY Order_id
My personal preference is the second approach, as I think it is clearer than the first in indicating which items are actually required for grouping, as opposed to which are merely being grouped to get round the ungrouped/unaggregated column problem.