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.
| Author |
Topic |
|
nimxoo
Starting Member
5 Posts |
 Posted - 2013-11-17 : 08:56:07
|
| i have a table name farmer data and it has attributes like farmer name, father name, pesticides, variety of crop etc...i have to write a query for Total no of people who has taken more than 1 variety of crop in a Season. how can i write this query. I have tried this query but its not giving me a single answer.select Farmer Name, Count(variety Of Crop) from farmer data group by farmer Name having count(Variety Of Crop)>1 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-11-17 : 09:03:54
|
what does the value of column [variety of crop] like ? 1, 2, 3 etc ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-11-17 : 09:20:44
|
Maybe:select count(*) as [farmers with multiple crops] from (select [farmer name] from [farmer data] group by [farmer name] having count([variety of crop])>1 ) as a |
|
|
|
nimxoo
Starting Member
5 Posts |
Posted - 2013-11-17 : 09:43:43
|
it has values like CIM699, MRE66 etc quote:
Originally posted by khtan
what does the value of column [variety of crop] like ? 1, 2, 3 etc ?
KH[spoiler]Time is always against us[/spoiler]
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-11-18 : 06:15:07
|
[code]SELECT FarmerNameFROM dbo.FarmerData GROUP BY FarmerName HAVING MIN(VarietyOfCrop) < MAX(VarietyOfCrop);[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|
|
Total no of people who has taken more than 1 varie