Query Building - SQL Server Forums

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

Query Building

Author

Topic

mike1973
Starting Member

46 Posts

Posted - 2010-05-10 : 10:03:47

Hello guys,
The following diagram represents 2 tables 1 for sales and one for purchases, the 3rd table will represent the dollar exchage value

I'm trying to qrite a query that will help me see each category in table Categories how much i purchases, total weight, total price and same thing for the sales table, all by categoryid in 1 query .

Is that possible?

Thanks in advance
mike

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 10:45:40

What fields are used in the 2 relationships shown on that diagram?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

mike1973
Starting Member

46 Posts

Posted - 2010-05-10 : 10:52:32

Sales.CategoryAutoID--->>>Categories.CategoryAutoID
AND
DailyOperations.CategoryID--->>>Categories.CategoryAutoID

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 11:07:22

This should point you in the right direction:

SELECT c.CategoryAR, c.CategoryEN, 
		ISNULL(SUM(s.TotalWeight * s.UnitPrice), 0) AS SalesTotal, 
		ISNULL(SUM(s.TotalWeight), 0) AS SalesWeight, 
		ISNULL(SUM(d.TotalWeight * d.UnitPrice), 0) AS DailyTotal, 
		ISNULL(SUM(d.TotalWeight), 0) AS DailyWeight
FROM Categories c
LEFT JOIN SalesProducts s
	ON c.CategoryAutoID = s.CategoryAutoID
LEFT JOIN DialyOperations d
	ON c.CategoryAutoID = d.CategoryID
GROUP BY c.CategoryAR, c.CategoryEN

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

mike1973
Starting Member

46 Posts

Posted - 2010-05-10 : 11:24:11

I'm getting this error on execution

The correlation name 's' is specified multiple times in a FROM clause.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 11:40:06

quote:
Originally posted by mike1973

I'm getting this error on execution

The correlation name 's' is specified multiple times in a FROM clause.

Sorry, there was a typo in my previous post. I've edited it now, so try that.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

mike1973
Starting Member

46 Posts

Posted - 2010-05-10 : 18:17:14

this is so weird, the columns SalesTotal and SalesWeight are giving wrong results while DailyTotal and DailyWeight are correct.
Please can you help?
Thanks a lot in advance

SELECT c.CategoryAR, c.CategoryEN,
ISNULL(SUM(d.Weight*d.UnitPrice), 0) AS DailyTotal,
ISNULL(SUM(d.Weight), 0) AS DailyWeight,
ISNULL(SUM(s.TotalWeight*s.UnitPrice), 0) AS SalesTotal,
ISNULL(SUM(s.TotalWeight), 0) AS SalesWeight
FROM Categories c
LEFT JOIN DailyOperations d ON c.CategoryAutoID = d.CategoryID
LEFT JOIN SalesProducts s ON c.CategoryAutoID = s.CategoryAutoID
GROUP BY c.CategoryAR, c.CategoryEN

mike1973
Starting Member

46 Posts

Posted - 2010-05-11 : 11:05:00

Please guys can you help on this one?
Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources