Using Variable Select - 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 2008 Forums

Transact-SQL (2008)

Using Variable Select

Author

Topic

latture
Starting Member

24 Posts

Posted - 2014-05-02 : 10:07:19

Hello!

I'm trying to see if I could do something like or find a way to do something like the below query statement but I'm getting an Incorrect syntax near my variable.

SELECT
STNO,
SUM(PRICE*QTY) AS @Criteria
FROM table
GROUP BY STNO
order by STNO

Thanks in advance!

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-02 : 10:22:15

Try @Criteria = SUM(...

djj

latture
Starting Member

24 Posts

Posted - 2014-05-02 : 10:25:39

Nope, I'm getting a SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-02 : 12:09:10

and remove the field

 STNO

from SELECT stmt

sabinWeb MCP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-02 : 12:10:41

That really doesn't make any sense as you can't assign multiple rows to a single variable like that. Well you *can*, but you are just going to get a random value.

Can you explain in words what you are trying to do.

Also, it is very helpful to post your schema, sample data in a consumable format and expected output. That makes it much easier for us to help you. If you are not familiar with how to do that, please follow the links below:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-02 : 14:45:26

Lamprey, I did not catch the multiple. That is what I get for answering too quickly.

djj

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-02 : 14:50:33

quote:
Originally posted by djj55

Lamprey, I did not catch the multiple. That is what I get for answering too quickly.

djj

It's possible that's what the OP wants, but hard to tell.. ;)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-02 : 17:22:13

You can use the code below. Because of the order by, I believe the variable will end up with the sum of the last STNO, but you can include the STNO to be sure, and to make the SQL valid.



SELECT 
@STNO = STNO, 
@Criteria = SUM(PRICE*QTY)
FROM table
GROUP BY STNO 
order by STNO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources