Avatar of niceoneishere
niceoneishere
 asked on

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
ASP.NETMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
RehanYousaf

8/22/2022 - Mon
Habib Pourfard

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

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

ASKER
If I group them Its saying that I have group each and every field like Order.Id, Order.Email and so on
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
fb1990

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

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

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RehanYousaf

It is still ambigous what you want
fb1990

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

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
RehanYousaf

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

ASKER
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

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;
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
fb1990

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

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

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

Thanks anyway for trying appreciate it
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
fb1990

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;
ASKER CERTIFIED SOLUTION
niceoneishere

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
fb1990

I am glad you found your answer.
RehanYousaf

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
niceoneishere

ASKER
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

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