[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

What Am I missing in this SQL Query?

I wrote a small SQL Query in SQL Server 2005 to return Customer Order Number, Email and their order details.

The query works but it returns the email address and order id multiple times as each customer can have more than one item in their order.

I am posting my Query. anyway to modify it so that only once the order number and email is returned even if the customer has more than one item

I think I am missing something very simple here.

SELECT [Order].Id, [Order].Email, [Order].PromotionCode, [Order].ShippingCost, OrderItem.ItemNumber, Size.Name, OrderItem.Price, Size.Id AS Expr1
FROM [Order] INNER JOIN OrderItem ON [Order].Id = OrderItem.OrderId
             INNER JOIN Size ON OrderItem.SizeId = Size.Id 
WHERE ([Order].PromotionCode = 'M123') OR ([Order].PromotionCode = 'T4444') OR ([Order].PromotionCode = 'WABD') OR ([Order].PromotionCode = 'Q695') OR ([Order].PromotionCode = 'P675')

Open in new window


Thanks and appreciate it
0
niceoneishere
Asked:
niceoneishere
  • 8
  • 7
  • 5
  • +1
1 Solution
 
Habib PourfardCommented:
As you mentioned there are more than one orderitem for an order. in this case on the final result OrderItem.Price and OrderItem.ItemNumber are not meaningful. so you need to exclude them or you can group by item and use aggregrate functions like sum(orderitem.pirce) instead of orderitem.price itself.
0
 
fb1990Commented:
try this....

SELECT distinct [Order].Id, [Order].Email, [Order].PromotionCode, [Order].ShippingCost, OrderItem.ItemNumber, Size.Name, OrderItem.Price, Size.Id AS Expr1
FROM [Order] INNER JOIN OrderItem ON [Order].Id = OrderItem.OrderId
             INNER JOIN Size ON OrderItem.SizeId = Size.Id
WHERE ([Order].PromotionCode = 'M123') OR ([Order].PromotionCode = 'T4444') OR ([Order].PromotionCode = 'WABD') OR ([Order].PromotionCode = 'Q695') OR ([Order].PromotionCode = 'P675')
0
 
niceoneishereAuthor Commented:
If I group them Its saying that I have group each and every field like Order.Id, Order.Email and so on
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
fb1990Commented:
I am not sure i understand what you are doing.  Are you trying to count the number of occurrence each order id?

Please advice...
0
 
RehanYousafCommented:
If you provide some sample data and desired result, it will help
0
 
niceoneishereAuthor Commented:
Nope, basically this is the desired output I am looking for

OrderId, Email, Shipping, PromotionCode, ItemNumber, Price, Size

and then bound these values to a GRIDVIEW so I can calculate the Total Price of all the items for a particular Order.


Thanks
0
 
RehanYousafCommented:
It is still ambigous what you want
0
 
fb1990Commented:
try this....

assumes price is numeric

SELECT [Order].Id, [Order].Email, [Order].PromotionCode, [Order].ShippingCost, OrderItem.ItemNumber, Size.Name, sum(OrderItem.Price) as Price, Size.Id AS Expr1
FROM [Order] INNER JOIN OrderItem ON [Order].Id = OrderItem.OrderId
             INNER JOIN Size ON OrderItem.SizeId = Size.Id
WHERE ([Order].PromotionCode = 'M123') OR ([Order].PromotionCode = 'T4444') OR ([Order].PromotionCode = 'WABD') OR ([Order].PromotionCode = 'Q695') OR ([Order].PromotionCode = 'P675')
GROUP BY
[Order].Id, [Order].Email, [Order].PromotionCode, [Order].ShippingCost, OrderItem.ItemNumber, Size.Name, Size.Id
0
 
niceoneishereAuthor Commented:
Ok here is some sample data.

From Orders tables

Order Id - 1001
Email - test@test.com
ShippingCost - 4.95
PromotionCode - T4444

Order Id - 1002
Email - test1@test.com
ShippingCost - 0.00
PromotionCode - WABD

Order Id - 1055
Email - test36@test.com
ShippingCost - 16.95
PromotionCode -

Now Samples for OrderItem table

OrderItem.Id - its an auto increment field as is a primary key
OrderItem.OrderId - 1001
OrderItem.Number - A123
OrderItem.Price - 43.95
OrderItem.Number - B345
OrderItem.Price - 9.95
OrderItem.Number - B456
OrderItem.Price - 15.45

OrderItem.Id - its an auto increment field as is a primary key
OrderItem.OrderId - 1002
OrderItem.Number - F123
OrderItem.Price - 93.95
OrderItem.Number - I345
OrderItem.Price - 19.95
OrderItem.Number - K456
OrderItem.Price - 15.45

OrderItem.Id - its an auto increment field as is a primary key
OrderItem.OrderId - 1055
OrderItem.Number - P123
OrderItem.Price - 93.95
OrderItem.Number - T345
OrderItem.Price - 19.95


At this point i am not that worried about Size.

now I am looking for my output as follows

OrderID, Email, ShippingCost, PromotionCode, ItemNumber, Price
1001, test@test.com, 4.95, T444, A123, 43.95
                                                    B345, 9.95
                                                    B456, 15.95

and then the next order

so like this I can get total price for each order.

Sorry for the confusion and hope this is little more clear
0
 
RehanYousafCommented:
To get this format you will require SSRS or Crystal Reports or something similar

OrderID, Email, ShippingCost, PromotionCode, ItemNumber, Price
1001, test@test.com, 4.95, T444, A123, 43.95
                                                    B345, 9.95
                                                    B456, 15.95

what you can get using sql is

OrderID, Email, ShippingCost, PromotionCode, ItemNumber, Price
1001, test@test.com, 4.95, T444, A123, 43.95
1001, test@test.com, 4.95, T444, B345, 9.95
1001, test@test.com, 4.95, T444, B456, 15.95

or

OrderID, Email, ShippingCost, PromotionCode, ItemNumbers, Price, TotalPrice
1001, test@test.com, 4.95, T444, A123-B345-B456, 43.95+9.95+15.95, 69.85
0
 
niceoneishereAuthor Commented:
hmm I thinking i am approaching this a wrong way. I think I need to tweak my query in such way that for every OrderID, I need to get all the details including order details.

I will try that and post my results.

Thanks
0
 
fb1990Commented:
here you go

SELECT order.Order_id, order.email, order.ShippingCost, order.PromotionCode, orderitem.Number, Sum(orderitem.Price) AS SumOfPrice, Sum([price]+[shippingcost]) AS ExtPrice
FROM [order] INNER JOIN orderitem ON order.Order_id = orderitem.Order_id
GROUP BY order.Order_id, order.email, order.ShippingCost, order.PromotionCode, orderitem.Number;
0
 
fb1990Commented:
the result

Order_id      email      ShippingCost      PromotionCode      Number      SumOfPrice      ExtPrice
1001      test@test.com      4.95      T4444      A123      9.95      14.9
1001      test@test.com      4.95      T4444      B345      15.45      20.4
1001      test@test.com      4.95      T4444      B456      43.95      48.9
1002      test1@test.com      0.00      WABD      F123      93.95      93.95
1002      test1@test.com      0.00      WABD      I345      19.95      19.95
1002      test1@test.com      0.00      WABD      K456      15.45      15.45
1055      test36@test.com      16.95            P123      93.95      110.9
1055      test36@test.com      16.95            T345      19.95      36.9
0
 
fb1990Commented:
I have used access to do this...

please let me know if you want me to attach the access database...the sql code shoud work for you regardless.
0
 
niceoneishereAuthor Commented:
Thanks for trying fb1990 but I think you misunderstood by my question.

Thanks anyway for trying appreciate it
0
 
fb1990Commented:
What about using a subquery like one:

SELECT Order_id,Email,PromotionCode,Sum(TotPrice) AS FinalPrice
FROM(
SELECT order.Order_id, order.email, order.ShippingCost, orderitem.Number, order.PromotionCode, Sum(orderitem.Price) AS SumOfPrice, Sum([price]+[shippingcost]) AS TotPrice
FROM [order] INNER JOIN orderitem ON order.Order_id = orderitem.Order_id
GROUP BY order.Order_id, order.email, order.ShippingCost, orderitem.Number, order.PromotionCode) tbl1
GROUP BY Order_id,Email,PromotionCode;
0
 
niceoneishereAuthor Commented:
Thanks for all your help. But I think I found the solution using my original Query and DataGrid in asp.net.

I ended up binding the query to data grid and based on different order ids Inserted a new blank row and do the totals there.

I have attached an screenshot of what i have got so far.


draft
Even thou I am repeating the email, I think this looks more organized for presentation and now i can write the code total price.

Thanks
0
 
fb1990Commented:
I am glad you found your answer.
0
 
RehanYousafCommented:
You can get this type of result but the problem occurs when there are too many records to show as execution time will be greater

Secondly, you won't be able to utilise some of the features of data grid, such as, sorting

To produce similar result as of your screenshot, here is my script

DECLARE @Order TABLE (
	Id INT,
	Email VARCHAR(100), 
	PromotionCode VARCHAR(100), 
	ShippingCost MONEY
)
INSERT INTO @Order VALUES (1001, 'test1@mail.com', 'WABD', 4.95)
INSERT INTO @Order VALUES (1002, 'test2@mail.com', 'WABD', 4.95)
INSERT INTO @Order VALUES (1003, 'test3@mail.com', 'WABD', 14.95)
INSERT INTO @Order VALUES (1004, 'test1@mail.com', 'T4444', 4.95)
INSERT INTO @Order VALUES (1005, 'test2@mail.com', 'WABD', 14.95)
INSERT INTO @Order VALUES (1006, 'test3@mail.com', 'WABD', 4.95)
INSERT INTO @Order VALUES (1007, 'test1@mail.com', 'M123', 14.95)
INSERT INTO @Order VALUES (1008, 'test2@mail.com', 'W123', 4.95)
INSERT INTO @Order VALUES (1009, 'test3@mail.com', 'M435', 4.95)
INSERT INTO @Order VALUES (1010, 'test1@mail.com', 'WABD', 4.95)

DECLARE @OrderItem TABLE (
	ItemNumber VARCHAR(100),
	OrderId INT,
	SizeId INT,
	Price MONEY
)
INSERT INTO @OrderItem VALUES ('Y0804', 1001, 401, 198)

INSERT INTO @OrderItem VALUES ('Y0804', 1002, 401, 198)
INSERT INTO @OrderItem VALUES ('Y0803', 1002, 310, 189)
INSERT INTO @OrderItem VALUES ('Y3305', 1002, 401, 52)

INSERT INTO @OrderItem VALUES ('Y0804', 1003, 401, 198)
INSERT INTO @OrderItem VALUES ('Y3305', 1003, 401, 189)
INSERT INTO @OrderItem VALUES ('Y2302', 1003, 207, 112)

INSERT INTO @OrderItem VALUES ('Y2302', 1004, 207, 112)
INSERT INTO @OrderItem VALUES ('Y3305', 1004, 401, 52)

INSERT INTO @OrderItem VALUES ('Y3207', 1005, 312, 38)

INSERT INTO @OrderItem VALUES ('X1304', 1006, 512, 58)

INSERT INTO @OrderItem VALUES ('X1005', 1007, 512, 56)

INSERT INTO @OrderItem VALUES ('X1304', 1008, 512, 58)
INSERT INTO @OrderItem VALUES ('X1005', 1008, 512, 56)

INSERT INTO @OrderItem VALUES ('Y0804', 1009, 401, 198)

INSERT INTO @OrderItem VALUES ('Y0804', 1010, 401, 198)
INSERT INTO @OrderItem VALUES ('Y0803', 1010, 310, 189)

DECLARE @Size TABLE (
	Id INT,
	Name VARCHAR(100)
)
INSERT INTO @Size VALUES (401, '7')
INSERT INTO @Size VALUES (310, '4')
INSERT INTO @Size VALUES (207, '2T')
INSERT INTO @Size VALUES (312, '5')
INSERT INTO @Size VALUES (512, '')

DECLARE @Result TABLE (
	OrderId VARCHAR(100),
	Email VARCHAR(100),
	PromotionCode VARCHAR(100),
	ShippingCost VARCHAR(100),
	TotalPrice VARCHAR(100),
	TotalPricePlusShipping VARCHAR(100),
	RowNo INT,
	ItemNo VARCHAR(100),
	ItemPrice VARCHAR(100),
	Size VARCHAR(100),
	SizeID VARCHAR(100)
)

DECLARE @x INT
DECLARE @y INT
DECLARE @OrderId INT

------------------------------------------------------------------
SELECT 
	o.Id AS OrderId, 
	o.Email, 
	o.PromotionCode, 
	o.ShippingCost,
	SUM(i.Price) AS TotalPrice,
	SUM(i.Price) + o.ShippingCost AS TotalPricePlusShipping,	
	ROW_NUMBER() OVER(ORDER BY Id) RowNo
INTO
	#SelectedOrder
FROM 
	@Order o
	INNER JOIN @OrderItem i ON o.Id = i.OrderId	
WHERE 
	PromotionCode IN ('M123', 'T4444', 'WABD', 'Q695', 'P675')
GROUP BY
	o.Id, 
	o.Email, 
	o.PromotionCode, 
	o.ShippingCost

SET @x = 1
SELECT 
	@y = COUNT(*)
FROM
	#SelectedOrder

------------------------------------------------------------------
WHILE @x <= @y
BEGIN
	SELECT 
		@OrderId = OrderId 
	FROM
		#SelectedOrder
	WHERE
		RowNo = @x
	
	INSERT INTO
		@Result
	SELECT 
		*,
		'',
		'',
		'',
		''
	FROM 
		#SelectedOrder
	WHERE
		OrderId = @OrderId

	INSERT INTO
		@Result
	SELECT 
		'',
		'',
		'',
		'',
		'',
		'',
		'',
		i.ItemNumber, 
		i.Price,
		s.Name, 
		i.SizeId
	FROM 
		@OrderItem i
		LEFT JOIN @Size s ON i.SizeId = s.Id 
	WHERE
		i.OrderId = @OrderId
		
	SET @x = @x + 1
END

------------------------------------------------------------------	
SELECT 
	OrderId,
	Email,
	PromotionCode,
	ShippingCost,
	TotalPrice,
	TotalPricePlusShipping,
	ItemNo,
	ItemPrice,
	Size,
	SizeID
FROM 
	@Result

------------------------------------------------------------------
DROP TABLE #SelectedOrder

Open in new window

0
 
niceoneishereAuthor Commented:
I found mine more easier for what I am trying to do, But I would like to give the points for everyone who helped ,me. I just don't know to do that while accepting my own solution, please advice and I will give the points

Appreciate it
0
 
RehanYousafCommented:
Well if you explain how you achieved you desired result in much simpler way ... it will be enough for me :-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now