Group By/sub query problems - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Group By/sub query problems

Author

Topic

netsports
Starting Member

11 Posts

Posted - 2005-08-15 : 19:08:30

I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :

SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID

FROM (SELECT b.Deal_ID

FROM btransactions b

WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN

orders o ON o .Rep_ID = s.ID INNER JOIN

b ON o.ID = b.Deal_ID

GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID

HAVING (SUM(b.orderamount) > 0)

???
.NetSports

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-15 : 19:14:52

Your problem is that you aliased the derived table twice, SalesReps and s.

Tara

netsports
Starting Member

11 Posts

Posted - 2005-08-15 : 19:23:38

quote:
Originally posted by tduggan

Your problem is that you aliased the derived table twice, SalesReps and s.

Tara

>WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN

Tara, I only aliased SalesReps as S only once

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-15 : 19:26:35

If SalesReps is a table, then you can't specify a table and a derived table in the FROM part. It's one or the other. If you need both, then you'll need an additional JOIN statement.

Here's the derived table in case you haven't heard that term before:
(SELECT b.Deal_ID FROM btransactions b WHERE b.BoardDate = '20050815')

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources