SQL statement displays each row 3 times

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL statement displays each row 3 times

Author

Topic

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 00:26:22

I am pulling data from 3 tables and when it displays each row displays 3 times. Any ideas what to do?

3 Tables
Employee: EmployeeID, LastName, FirstName, Region
Orders: OrderID, EmployeeID
OrderDetail: OrderID, UnitPrice, Quantity, Discount%

SQL Statement:

SELECT e.EmployeeID, e.LastName, e.FirstName, e.Region,
 o.OrderID, (od.UnitPrice * od.Quantity) AS Total 
FROM
Employees e, Orders o, [Order Details] od
WHERE o.EmployeeID = 3 AND e.EmployeeID = o.EmployeeID AND od.OrderID = o.OrderID

Also I need to subtract the Discount% from the Total but I am not sure how to do that. So it would have to be (od.UnitPrice * od.Quantity - Discount%)
Thanks in advance

~Silke~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 00:48:18

[code]
SELECT e.EmployeeID,
e.LastName,
e.FirstName,
e.Region,
o.OrderID,
od.Total,
od.Total * ((100 - Discount) / 100.0) AS Nett_Total
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID
INNER JOIN (
SELECT OrderID,
SUM(od.UnitPrice * od.Quantity) AS Total
FROM [Order Details] as od
GROUP BY OrderID
) od ON od.OrderID = o.OrderID
WHERE o.EmployeeID = 3
[/code]

KH
[spoiler]Time is always against us[/spoiler]

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 01:19:22

That didn't quite work for me I got error messages. However, I got the INNER JOIN in there. So that's a good thing but I still get multiple rows of the same data.
So this is what I have.

SELECT e.EmployeeID, e.LastName, e.FirstName, e.Region,
 o.OrderID, (od.UnitPrice * od.Quantity) AS Total 
FROM
Employees e INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID INNER JOIN [Order Details] od
ON od.OrderID = o.OrderID
WHERE o.EmployeeID = 3

Any other ideas why it would display multiple rows of the same data?

~Silke~

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 01:50:29

This is because there are 3 entries in Order Details and there is no grouping.
KH's solutuon should work - what is the error message?

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 01:52:34

edited the query.

KH
[spoiler]Time is always against us[/spoiler]

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 02:04:54

Right now it says invalid column name Discount. I tried to change it to od.Discount and it does the same thing. :(

~Silke~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 02:06:57

quote:
Originally posted by snssewell

Right now it says invalid column name Discount. I tried to change it to od.Discount and it does the same thing. :(

~Silke~

the discount is in percentage or $ ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 02:08:08

[code]
SELECT e.EmployeeID,
e.LastName,
e.FirstName,
e.Region,
o.OrderID,
od.Total
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID
INNER JOIN (
SELECT od.OrderID,
SUM((od.UnitPrice * od.Quantity) * (100 - od.Discount) / 100.0) AS Total
FROM [Order Details] AS od
GROUP BY od.OrderID
) od ON od.OrderID = o.OrderID
WHERE o.EmployeeID = 3

[/code]

KH
[spoiler]Time is always against us[/spoiler]

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 02:10:28

Its in percentage. So the column would say either 0% or 15% or 20% etc.

~Silke~

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-02 : 02:10:43

Maybe it should be [Discount%] - see first post.

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 02:15:28

see my last query

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 02:18:17

quote:
Originally posted by webfred

Maybe it should be [Discount%] - see first post.

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks. Didn't noticed that

KH
[spoiler]Time is always against us[/spoiler]

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 02:22:52

quote:
Originally posted by khtan

see my last query

KH
[spoiler]Time is always against us[/spoiler]

YAY....that did it!!! You are good. Ok just one little thing. Is there a way to display the Total with 2 decimal places using the SQL statement?

~Silke~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 02:24:30

convert(decimal(20,2), od.Total) as Total

KH
[spoiler]Time is always against us[/spoiler]

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 02:31:58

quote:
Originally posted by khtan

convert(decimal(20,2), od.Total) as Total

KH
[spoiler]Time is always against us[/spoiler]

I tried to put it here:

SUM((od.UnitPrice * od.Quantity) * (100 - od.Discount) / 100.0) convert(decimal(20,2), od.Total) AS Total

but now it says Incorrect syntax near the keyword convert
Sorry I am riding the short bus

~Silke~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 02:33:24

not here. put the convert() in the main query


SELECT 		e.EmployeeID, 
		e.LastName, 
		e.FirstName, 
		e.Region,
 		o.OrderID, 
		convert(decimal(20,2), od.Total) as Total
FROM		Employees e

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 02:36:28

Actually depending on your business rule. round() first or sum() first.

You may want to put in the inner query if you need to convert to 2 decimal places before summation.

Putting in the main query means, when calculation of total, if the calculation may result in 3 or 4 decimal places and it is all total up before converting into 2 decimal places.

KH
[spoiler]Time is always against us[/spoiler]

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 02:37:42

Thank you that worked. Perfect. Thanks so much!!!!

~Silke~

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 02:59:45

quote:
Originally posted by khtan

Actually depending on your business rule. round() first or sum() first.

You may want to put in the inner query if you need to convert to 2 decimal places before summation.

Putting in the main query means, when calculation of total, if the calculation may result in 3 or 4 decimal places and it is all total up before converting into 2 decimal places.

KH
[spoiler]Time is always against us[/spoiler]

I see what you are saying. Thanks for the tip. The way it is now is actually fine for now. I do have another question though. Is there a way to use the ROLLUP with the code you gave me to calculate the total amount of all Orders? I been playing around with it but have not been able to get it to work yet. Not quite sure where to put it.

~Silke~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 03:08:06

[code]
SELECT e.EmployeeID,
e.LastName,
e.FirstName,
e.Region,
o.OrderID,
CONVERT(decimal(20,2), od.Total) AS Total,
CONVERT(decimal(20,2), ao.Total) AS All_Total
FROM Employees e
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID
INNER JOIN (
SELECT od.OrderID,
SUM((od.UnitPrice * od.Quantity) * (100 - od.Discount) / 100.0) AS Total
FROM [Order Details] AS od
GROUP BY od.OrderID
) od ON od.OrderID = o.OrderID
CROSS JOIN (
SELECT SUM((od.UnitPrice * od.Quantity) * (100 - od.Discount) / 100.0) AS Total
FROM [Order Details] AS od
) ao
WHERE o.EmployeeID = 3

[/code]

KH
[spoiler]Time is always against us[/spoiler]

snssewell
Starting Member

14 Posts

Posted - 2009-06-02 : 03:15:56

Gives me Incorrect syntax near the keyword CONVERT,
Incorrect syntax near 'od'
Incorrect syntax near 'ao'

:(

~Silke~

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources