What Am I missing in this SQL Query?

niceoneishere
niceoneishere used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Habib PourfardSoftware Developer
Top Expert 2012

Commented:
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.
fb1990

Commented:
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')
niceoneishere

Author

Commented:
If I group them Its saying that I have group each and every field like Order.Id, Order.Email and so on
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Access the MQ here
fb1990

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

Please advice...
RehanYousaf

Commented:
If you provide some sample data and desired result, it will help
niceoneishere

Author

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
RehanYousaf

Commented:
It is still ambigous what you want
fb1990

Commented:
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
niceoneishere

Author

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
RehanYousaf

Commented:
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
niceoneishere

Author

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
fb1990

Commented:
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;
fb1990

Commented:
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
fb1990

Commented:
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.
niceoneishere

Author

Commented:
Thanks for trying fb1990 but I think you misunderstood by my question.

Thanks anyway for trying appreciate it
fb1990

Commented:
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;
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
fb1990

Commented:
I am glad you found your answer.
RehanYousaf

Commented:
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

niceoneishere

Author

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
RehanYousaf

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial