1

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?

9
  • 1
    Are you able to modify the table schema? That is really what is needed here as there is no way of linking the two different orders to each other.
    – MrTCS
    Aug 22 '16 at 18:19
  • Unfortunately I am not able to modify table structure (that would alleviate 200% of the problems if I could.) Aug 22 '16 at 18:21
  • You can't do this reliably then. There needs to be something that says that it is the Orange Socks order that was Shipped Reviewed rather than the Blue Trousers one. Aug 22 '16 at 18:28
  • Would the query reliably return results if I ONLY returned an orderqty and an orderprice? Or would those results be skewed also? Aug 22 '16 at 18:30
  • There is no way the example data you have presented can ever return reliable results. SQL Server can't guess which of meh33's orders in table 1 matches to that in table 2. Aug 22 '16 at 18:35
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.