Solved

What Am I missing in this SQL Query?

Posted on 2012-12-28
21
250 Views
Last Modified: 2013-01-27
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
Comment
Question by:niceoneishere
  • 8
  • 7
  • 5
  • +1
21 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38727259
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
 
LVL 1

Expert Comment

by:fb1990
ID: 38727278
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
 
LVL 1

Author Comment

by:niceoneishere
ID: 38727341
If I group them Its saying that I have group each and every field like Order.Id, Order.Email and so on
0
 
LVL 1

Expert Comment

by:fb1990
ID: 38727364
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
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38727372
If you provide some sample data and desired result, it will help
0
 
LVL 1

Author Comment

by:niceoneishere
ID: 38727376
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
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38727403
It is still ambigous what you want
0
 
LVL 1

Expert Comment

by:fb1990
ID: 38727407
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
 
LVL 1

Author Comment

by:niceoneishere
ID: 38727440
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
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38727567
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:niceoneishere
ID: 38727611
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
 
LVL 1

Expert Comment

by:fb1990
ID: 38727669
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
 
LVL 1

Expert Comment

by:fb1990
ID: 38727682
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
 
LVL 1

Expert Comment

by:fb1990
ID: 38727691
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
 
LVL 1

Author Comment

by:niceoneishere
ID: 38727694
Thanks for trying fb1990 but I think you misunderstood by my question.

Thanks anyway for trying appreciate it
0
 
LVL 1

Expert Comment

by:fb1990
ID: 38727756
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
 
LVL 1

Accepted Solution

by:
niceoneishere earned 0 total points
ID: 38728030
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
 
LVL 1

Expert Comment

by:fb1990
ID: 38728167
I am glad you found your answer.
0
 
LVL 5

Expert Comment

by:RehanYousaf
ID: 38730646
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
 
LVL 1

Author Closing Comment

by:niceoneishere
ID: 38823890
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
 
LVL 5

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now