Sql Help

I'm trying to determine sales, discount, cost and profit.
The discount is not correct, it is calculated way too high.
Most likely has to do with the join on the line items?


SELECT 
	SUM(a.Price * a.Quantity) AS Sales, 
	SUM(b.discount) AS Discount, 
	SUM(a.Quantity * p.Cost) AS Cost,
	SUM(a.Price * a.Quantity) - SUM(b.discount) - SUM(a.Quantity * p.Cost) AS Profit
FROM Lineitems a
INNER JOIN Orders b
ON a.OrderID = b.OrderID
INNER JOIN Products P
ON a.ProductID = p.ProductID
	WHERE 
		b.GatewaySuccessful = 1
	AND 
		b.CustomerID <> 1000
	AND
		b.OrderTypeID = 1	
	AND	
		b.OrderStatusID IN (1, 2, 3)	
	AND 
		b.CreatedDate >= '09/13/2013'
	AND 
		b.CreatedDate < '09/14/2013'

Open in new window

LVL 8
JRockFLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'd throw in a WHERE b.orderID = {A single order with more than one product}, run the query, and eyeball whether the discout is being summed up more than once.

>FROM Lineitems a
>INNER JOIN Orders b
It would be infinately more readable to use aliases that give a clue to what the table is, such as li for Lineitems and o for Orders, than to use a, b, ...
0
PadawanDBAOperational DBACommented:
Are you trying to figure this out based on order, product, line item or what?  If you could give more information on what you're hoping to accomplish, that would be helpful!
0
JRockFLAuthor Commented:
Here is a more simplified version.

When I do this, the discount is 49.44
when it should be 16.48

There are 3 line items

SELECT 
	SUM(b.discount)
FROM 
	Lineitems a
INNER JOIN Orders b
ON a.OrderID = b.OrderID
	WHERE 
		b.GatewaySuccessful = 1
	AND 
		b.CustomerID <> 1000
	AND
		b.OrderTypeID = 1	
	AND	
		b.OrderStatusID IN (1, 2, 3)	
	AND 
		b.CreatedDate >= '09/13/2013'
	AND 
		b.CreatedDate < '09/14/2013'	
	AND
		b.OrderID=55774		

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

JRockFLAuthor Commented:
I"m trying to calculate the sales, discount, cost profit for specified order types in a specified period of time
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
16.418 x 3 = 49.44, and you have three line items.

The T-SQL is down to the line item level, and discount is at the order level.

Can you do something like...
WITH LineItems AS (
-- Line Item aggregated info
SELECT 
	a.orderID as OrderID, 
	SUM(a.Price * a.Quantity) AS total_sales, 
	SUM(a.Quantity * p.Cost) AS total_cost
FROM Lineitems a
	INNER JOIN Products P ON a.ProductID = p.ProductID
GROUP BY a.OrderiD)
SELECT o.OrderID, LineItems.total_sales - LineItems.total_cost - o.Discouts as total_profit
FROM Orders o
	JOIN LineItems on o.orderID = LineItems.OrderID
WHERE -- add your WHERE stuff here.

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JRockFLAuthor Commented:
Thank you jimhorn. I think that is the direction I need to head.
0
PortletPaulfreelancerCommented:
SUM(b.discount) would be multiplied for every matching row found in lineitems (a)

try this as line 2 of the simplified query:

      SUM(b.discount), MAX(b.discount), MIN(b.discount), COUNT(a.id)

-- assumes (a.id) is a unique field in lineitems

{sorry, an edit, forgot a word}
0
PortletPaulfreelancerCommented:
sorry Jim, didn't see yours. ignore mine please.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.