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
>

adding client server aliases

How can I import a client alias list in SQL Server 2005 Configuration Manager? The tool provides a way to export a list, but not to import a list. Adding 100 SQL Server instances via the GUI is time consuming and agonizing.

Thanks,

Justin Randall

Unfortunately, as you noticed, the functionality to import a list is missing.

If you think it would be a useful addition, please let your wishes/needs be known at:

Suggestions for SQL Server

http://connect.microsoft.com/sqlserver

|||I will make the suggestion. Thanks!