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?
4 Answers
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
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.
In your scenario it doesn't seem correct to group-by Order_id having varying Article_id. You expected result seems wrong. It should be:
PR10.001 has two Article_id 20.001a, 41.022b and total quantity 4 (not 3)
Is your expectation is to get total quantity for each Order_id but retaining varying column too then you need to decide on expected result? something like:
Order_id Customer_id Article_id Delivery_date Quantity
---------------------------------------------------------------------------
PR10.001 11 20.001a, 41.022b 17-04-2013 4
PR13.001 15 41.022b 19-04-2013 2
I have used similar kind of table as yours. names would be different jsu to test it fast. Please refer to the following query. Modify names as you require.
select ordid,customerid,articleid,SUM(cast(quantity as numeric)) quantity from test_stack group by ordid,customerid,articleid
