Group By/sub query problems

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)
?
NetSportsYou can only give a subquery one alias, you are trying to give it two
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s
Should be:
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') s
However, b is not in your outer query either.
The absolute best way to get a specific and useful answer is to post proper
specs (DDL, sample data, desired results). Check out
http://www.aspfaq.com/5006 which will help you get more constructive
responses.
"netsports" <ballz2wall@.cox-dot-net.no-spam.invalid> wrote in message
news:3bSdneDoYqzc2p_eRVn_vQ@.giganews.com...
> 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
>