-2

I want total orders and total revenue on 31st dec. But in this query so many duplicate values are there..

select *
from Orderdetails od inner join
     OrderProducts op
     on od.orderid=op.orderid inner join
     addonProducts ap
     on ap.addonproductid=op.productid  
where od.DeliveryDate between convert(datetime,Convert(varchar(50), '12/31/2014',101)) and
                              convert(datetime, Convert(varchar(50), '01/02/2015',101))  and
      Datepart(Month, od.DeliveryDate) = '12' and DATEPART(DAY,od.DeliveryDate) = '31' and
      od.TransactionId is not null and ap.addonCategoryId='1002'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Myself
  • 13
  • 4
  • You need aggregates with group by – Mihai Jan 05 '16 at 12:24
  • ya.. but in the result i got so many duplicate values like... customerid:50109 48086 50109 48086 50109 48086 50109 36469 – Myself Jan 05 '16 at 12:27
  • what about using select distinct? – Greg White Jan 05 '16 at 12:28
  • 1
    can you share share sample data – bmsqldev Jan 05 '16 at 12:28
  • I want unique values and using aggregate functions... – Myself Jan 05 '16 at 12:30
  • OrderID CustomerId CustomerFirstName DeliveryDate TransactionId GA12272014063628703 50109 Vidhya 12/31/2014 9D694287NA7954709 GA12312014132804290 48086 Kalyan Chakravarthy 12/31/2014 232fb618642060ef6cb4 GA12272014063628703 50109 Vidhya 12/31/2014 9D694287NA7954709 – Myself Jan 05 '16 at 12:34
  • What do you want to group by - or do you just want a total for the day? Where is the price that the revenue is based on held - in orderproducts or Orderdetails? Is there a one-to-many relationship between orderproducts and addonproducts? Currently we need to be second-guessing your database schema to try to answer... – strickt01 Jan 05 '16 at 12:48
  • 1
    @SushmaSatya SO has a [handy guide](http://stackoverflow.com/help/mcve) on how to provide useful sample data. Try following these tips and you'll likely find it's easier for us to help you. – David Rushton Jan 05 '16 at 14:22

1 Answers1

0

In the absence of any schema details:

select count(distinct od.transactionId), sum(op.productprice)
from Orderdetails od inner join
 OrderProducts op
 on od.orderid=op.orderid inner join
 addonProducts ap
 on ap.addonproductid=op.productid  
where od.DeliveryDate between '2014-12-31' and '2015-01-01'and
  od.TransactionId is not null and ap.addonCategoryId='1002'

This will give you the order total and revenue for the day assuming:

  • there is no need to group by any particular columns (such as product name or date)
  • a one-to-many relationship between Orderdetails and OrderProducts
  • a one-to-one relationship between OrderProducts and addonProducts with a given addonCategoryId
  • a productprice column in OrderProducts from which the revenue is derived
strickt01
  • 3,959
  • 1
  • 17
  • 32