What Am I missing in this SQL Query?

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.

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')

If I group them Its saying that I have group each and every field like Order.Id, Order.Email and so on

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

Please advice...

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

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

It is still ambigous what you want

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

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

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

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

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;

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

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.

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

Thanks anyway for trying appreciate it

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;

I am glad you found your answer.

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

Select all Open in new window

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

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