Adding the sum of column to use as alias

Chumley,
I've double-checked the syntax of the statements I posted, and they're OK.
Have you altered it them any way? Post the exact statement you're executing
and I'll have a look.
Also, which version of SQL Server are you working in?
Thanks
Damien
"Chumley Walrus" wrote:

> Damien, I now get an "Invalid syntax near SUM " error from the sql
> string you have outlined (pointing to the HAVING SUM line). I don't
> understand,a s I know there's data in there meeting this criteria.
>Right,
I'll spare you the 'dynamic sql is bad' stuff, because if you read this
group regularly, you already know.
You are missing a plus sign after your GROUP BY clause, and you cannot use
an alias in your HAVING. In the SQL I posted for you, I put:
SELECT salesperson, SUM( saleamount ) AS allsales
FROM #transactions
--WHERE thedate In ('20050106', '20050206')
GROUP BY salesperson
HAVING SUM( saleamount ) > 0
So you can see, you don't use 'allsales', you use SUM ( saleamount ).
Being rude to Joe is not going to help you. He's earned his right to make
comments like that be being one of the leading authorities in SQL in the
entire world. Even if you don't agree with his point of view, you've at
least got to respect it.
Let me know how you get on with that SQL.
Damien
"Chumley Walrus" wrote:

> I have inner joins (they all work, as the various IDs are related, and
> do fine in my main sql string), I would post the DDL, but I know the
> datatypes are absolutely accurate (saleamount is a money datatype)
> SELECT ticket.Salesperson_ID, employ.LName + ', ' + tblSalesRep.FName
> AS Salesperson, " +
> "ticket.ID, employ.ID, " +
> "transaction.thedate, " +
> "SUM(transactions.saleamount) AS allsales,
> transactions.ticket_ID,transactions.thedate " +
> "FROM ticket " +
> "INNER JOIN employ ON ticket.Salesperson_ID = employ.ID " +
> "INNER JOIN transactions ON ticket.ID=transactions.ticket_ID " +
> "WHERE transactions.theddate IN ('6/1/2005', '6/2/2005')" +
> "GROUP BY Salesperson"
> "HAVING SUM(allsales) > 0 ";
> once again, I get an "Invalid syntax error by SUM" on HAVING
> SUM(allsales) line.
> '
> chumley
>