July 1, 2009 at 10:46 am
Hi,
i've got a table of towns:
id id_state name indicative
the following query returns the states wich have more than one town:
select id_state , count(*)
from towns
group by id_state
having count(*) > 1;
this query runs ok , but when i try to put the column "name" on the select, then i must put it in the group by as well:
select id_state , name,count(*)
from towns, name
group by id_state
having count(*) > 1;
after execute it there is no result because i added the column "name" in the group by
if anyone have an idea about develop this query or knows how can i put a column in select but not in group by or an agregated function i'd be thankfull..
Pd. excuse my english but spanish is my mathern language.
July 1, 2009 at 12:37 pm
Try something like this:
select s.id_state, t.name
from
(
select id_state
from towns
group by id_state
having count(*) > 1
)s
join towns t
on s.id_state = t.id_state
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 1, 2009 at 2:22 pm
thanks DCPeterson for the answer, and viewing it, i realized that i was in a mistake with the original query
cause the names that i wanna know are the states name (not the town names), so with a join beteen towns and the states table who i haven't mentioned, the solution is done,
the query will be:
select s.id_state, t.state_name, count(*)
from
towns s
join states t
on s.id_state = t.id_state
group by s.id_state, t.state_name
having count(*) >1
Once again thanks for make me figured out i was mistaken.
July 1, 2009 at 3:41 pm
Thank you for posting your solution.
To answer your first question (how to avoid putting name in the group by), you can use an aggregrate function on the name in the select list:
select state_id, max(name), min(name), count(*)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply