Subquery Error, only one expression

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)

Subquery Error, only one expression

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-04-27 : 09:55:58

Hi,

I am trying to find the totals for each month from a table and set this into a variable. However I am getting an error.

Msg 116, Level 16, State 1, Line 19
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can anyone help?


declare @total int


set @total = (
	select 
		SUM( a.amount ),
		month( c.created )
	from budget_categories c
	INNER JOIN budget_category_amount as a
	ON c.id = a.incomeID
	INNER JOIN budget_types as t
	ON t.id = c.typeid	
	where c.active = 1
	GROUP BY Month(c.created)
	 )

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-27 : 10:00:41

GROUP BY Month(c.created)

You will get a value per group (month). @total can only hold one value - which do you want and what do you want to do with the others?
Looks like you are confused about sets and variables.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-27 : 10:04:27

1- Subquery will return two columns. HOW can you save two column values in single parameter
2- Subquery will return a separate for each month. It mean multiple rows will return. HOW can you save multiple row in a single variable

your query only will be valid as follow

declare @total int

set @total = (
select
SUM( a.amount ),
~~month( c.created~~ )
from budget_categories c
INNER JOIN budget_category_amount as a
ON c.id = a.incomeID
INNER JOIN budget_types as t
ON t.id = c.typeid
where c.active = 1
GROUP BY Month(c.created)
HAVING Month(c.created) = 1 -- OR YOUR REQURED MONTH

)

--------------------------
http://connectsql.blogspot.com/

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-04-27 : 10:11:30

You guys are right, my logic is screwed up, I am trying to save multiple vars in one variable.

Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-27 : 10:14:41

You can save them into a table variable, if needed.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources