SQL Server Adding one row to another but only for one column

Question

Probably best understood by the picture below.

. I want to add together any items that share a reference number without adding up the table number column. My SQL is of the form

SELECT

[Order Reference]
,[Item Price]
,[Table Number]

From Orders

I'm pretty sure I need to SELECT from a second SELECT that sums the items prices but I can't get the exact syntax for summing over the order reference without summing the table numbers also.

Edit :

I think I left out important info for the first answerers. And I clearly messed up the sample data. Many apologies. The table has other columns that I haven't included in my query so when I try a group by I still get multiple rows. I want to isolate the particular column and merge the rows disregarding any column that is different (I don't mind which data of the other columns is chosen)

So for clarity (hopefully) the table also has 20 other columns like discount yes or no which I basically want to ignore but which seem to prevent the rows from merging together from a simple group by clause.

Answer 1

You need to SUM() up the Items and GROUP your result BY your (desired unique) value(s).

SELECT [Order Reference],
       SUM(PRICE) as 'Items Total',           
       [Table Number] as 'Table Number'
FROM Orders
Group By [Order Reference], [Table Number]

Note:

When working with aggregate functions such as SUM, MAX and others you have to group by every column you want to display in your SELECT.

Answer 2

try this query to find your answer

SELECT Order_Reference, SUM(Item_Price) AS 'Total_Item', Table_Number 
FROM Your_Table_Name
GROUP BY Order_Reference,Table_Number 
ORDER BY Order_Reference ASC;

Answer 3

Use below query to get expected result

SELECT ORDER_REFERENCE, SUM(ITEM_PRICE) AS ITEMS_TOTAL, TABLE_NUMBER FROM TABLE_NAME
GROUP BY ORDER_REFERENCE, TABLE_NUMBER  ORDER BY TABLE_NUMBER;

Answer 4

Why not use Order By clause for the two column OrderReference and TableNumber.

You can use the below query but this will contradit the result table you have shown as the One OrderReference will produce two output since there TableNumber is different.

SELECT orderreference, 
       Sum(itemprice) AS ItemTotal, 
       tablenumber 
FROM   so_orders 
GROUP  BY orderreference, 
          tablenumber 
ORDER  BY orderreference