February 28, 2008 at 1:03 am
i have created this sql command...
select count(role_name) as Total, role_name
from role
i got this message....
Msg 8120, Level 16, State 1, Line 1
Column 'role.Role_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
whats wrong wof my sql command?
any suggestion?
February 28, 2008 at 1:35 am
count returns a single result per group. If you do not use group by then there is only one rowgroup. So in your query there is one count and one group, so it would result in one row.
At the same time you would like to get back all the rows as well by asking ro the role_name.
What are you trying to query? If you are trying to get the total number of role names then try:
select count(role_name) as Total from role
if you want to get a list of roles names and the number of their occurances, you can do:
select count(role_name) as Total, role_name
from role
group by role_name
Regards,
Andras
February 28, 2008 at 1:35 am
Well the problem is you put an aggregate function into a query as well as a column name, but when you do this (for example SUM(col)) you need to tell SQL server how it should count those role_name columns, ie. how to group the results.
Thus, your query should look like this
select count(role_name) as Total, role_name
from role
group by role_name
Andras explained it much better...we posted at the same time....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply