Sql-server – Query To Sum Order Totals

sql serversql-server-2008-r2t-sql

I have two tables that I need to pull data from. I need to produce an order total in the query. Should be straight forward, but I get inaccurate results for anyone who has multiple orders. This is my DDL

Create Table #first
(
  userid varchar(100)
  ,orderqty int
  ,orderprice float
  ,orderdesc varchar(500)
  ,orderdate datetime
)
Create Table #second
(
  userid varchar(100)
  ,orderstatus varchar(100)
  ,orderreviewstatus varchar(100)
)

Insert Into #first VALUES
('emp12', '14', '10', 'firstorder', '2016-08-11 13:59:23.363')
,('bla', '1', '20', 'red socks', '2016-08-11 13:59:23.363')
,('gra23', '2', '4', 'black coat', '2016-08-11 13:59:23.363')
,('meh33', '5', '2', 'orange socks', '2016-08-11 13:59:23.363')
,('meh33', '3', '1', 'blue trousers', '2016-08-13 13:59:23.363')

Insert Into #second VALUES
('emp12', 'Shipped', 'Reviewed')
,('bla', 'Shipped', 'Reviewed')
,('gra23', 'Shipped', 'Reviewed')
,('meh33', 'Shipped', 'Reviewed')
,('meh33', NULL, NULL)

In this example only userid meh33 has two orders, and this is my query I am using

Select
a.userid
,a.orderqty
,a.orderprice
,SUM(a.orderqty*a.orderprice) As OrderTotal
,a.orderdesc
,a.orderdate
,b.orderstatus
,b.orderreviewstatus
FROM #first a
INNER JOIN #second b
ON a.userid = b.userid
GROUP BY a.userid, a.orderqty, a.orderprice, a.orderdesc, a.orderdate,     b.orderstatus, b.orderreviewstatus

This is the output that this query produces (notice the multiple entries for meh33:

userid  orderqty    orderprice  OrderTotal  orderdesc   orderdate   orderstatus orderreviewstatus
bla 1   20  20  red socks   2016-08-11 13:59:23.363 Shipped Reviewed
emp12   14  10  140 firstorder  2016-08-11 13:59:23.363 Shipped Reviewed
gra23   2   4   8   black coat  2016-08-11 13:59:23.363 Shipped Reviewed
meh33   3   1   3   blue trousers   2016-08-13 13:59:23.363 NULL    NULL
meh33   3   1   3   blue trousers   2016-08-13 13:59:23.363 Shipped Reviewed
meh33   5   2   10  orange socks    2016-08-11 13:59:23.363 NULL    NULL
meh33   5   2   10  orange socks    2016-08-11 13:59:23.363 Shipped Reviewed

And this is my desired output: (one row for each order)

userid  orderqty    orderprice  OrderTotal  orderdesc   orderdate   orderstatus orderreviewstatus
bla 1 20 20 red socks 2016-08-11 13:59:23.363 Shipped Reviewed
emp12 14 10 140 firstorder 2016-08-11 13:59:23.363 Shipped Reviewed
gra23 2 4 8 black coat 2016-08-11 13:59:23.363 Shipped Reviewed 
meh33 5 2 10 orange socks 2016-08-11 13:59:23.363 Shipped Reviewed
meh33 3 1 3 blue trousers 2016-08-13 13:59:23.363

What must I alter in my query in order to get my desired result set returned?

Best Answer

You need to change you GROUP BY and SELECT clauses. Also, I'd use Order_ID instead of description to sort.

Select a.userid ,
       SUM(a.orderqty) AS OrderQtyTotal ,
       SUM(a.orderprice) AS OrderPriceTotal ,
       SUM(a.orderqty*a.orderprice) AS OrderTotal ,
       a.orderdesc ,
       MAX(a.orderdate) AS LatestOrderDate ,
       MAX(b.orderstatus) AS LatestOrderStatus ,
       MAX(b.orderreviewstatus) AS LatestOrderViewStatus 
FROM #first a INNER JOIN #second b ON a.userid = b.userid 
GROUP BY a.userid, a.orderdesc
Related Question