Number of distinct farmers in each town

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

General SQL Server Forums

New to SQL Server Programming

Number of distinct farmers in each town

Author

Topic

nimxoo
Starting Member

5 Posts

Posted - 2013-11-17 : 10:08:23

-1 down vote favorite

I have a table name farmer data and it has attributes like farmer name, father name, pesticides used, town etc (these attributes would be required for query). also the data type of columns is var char.
I have to write a query to calculate the average no of farmers in each town.
How can I write this query? I have tried this query but its not giving me the count of all the farmers in each town. its not giving me the count of distinct farmers

select town, count (*)
from farmer_data
group by town

Prav4u
Starting Member

15 Posts

Posted - 2013-11-17 : 10:48:31

try this
select town, count (distinct farmername)
from farmer_data
group by town

Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com

nimxoo
Starting Member

5 Posts

Posted - 2013-11-17 : 12:46:05

but its giving total number of distinct farmers against every town name. its not giving distinct farmers in each town like below

town name farmers

Ch. 5-M 899
Ch. 77 M 899
Chak 60M 899
Gazipur 899
Hootwala 899

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-17 : 22:50:21

quote:
Originally posted by nimxoo

-1 down vote favorite

I have a table name farmer data and it has attributes like farmer name, father name, pesticides used, town etc (these attributes would be required for query). also the data type of columns is var char.
[redI have to write a query to calculate the average no of farmers in each town.
How can I write this query? I have tried this query but its not giving me the count of all the farmers in each town. its not giving me the count of distinct farmers[/red]

select town, count (*)
from farmer_data
group by town

What do you want actually ? You mention in your post
(1) "average no of formers"
(2) "count of all the farmers"
(3) "count of distinct farmers"

so it is (1) or (2) or (3) ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-11-18 : 05:53:13

[code]SELECT fd.Town,
fd.Name,
x.UniqueFarmers
FROM dbo.FamersData AS fd
CROSS JOIN (
SELECT COUNT(DISTINCT Name) AS UniqueFarmers
FROM dbo.FarmersData
) AS x;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

nimxoo
Starting Member

5 Posts

Posted - 2013-11-19 : 15:37:40

i want no of distinct farmers in every town. this is my sample data
Farmer name town

Taimoor Shah Ch. 5-M

NUSRAT ali Ch. 77 M

kamal khan Hootwala

ABDUL KHALIQ Gazipur

Anwar Kamal Hootwala

NUSRAT ali Ch. 77 M

now i want distinct number of farmers in every town.. i have 25 million records and i found distinct number of farmers with a query and its returning me 899. now issue is this query is returning me 899 against every town name. like in this query, output should be

town name farmers in each town

Ch. 5-M 1

Ch. 77 M 2

Gazipur 1

Hootwala 2

instead of giving this result, its giving me total number of farmers against each town, like in this scenario, output which i am getting is

town name farmers in each town

Ch. 5-M 6

Ch. 77 M 6

Gazipur 6

Hootwala 6

i hope my question is clear now..

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-11-19 : 16:48:48

Learn how to create temp table with sample data and provide the business rules and expect out put so other people can help you. Very basic things but people don't get it.

quote:
Originally posted by nimxoo

i want no of distinct farmers in every town. this is my sample data
Farmer name town

Taimoor Shah Ch. 5-M

NUSRAT ali Ch. 77 M

kamal khan Hootwala

ABDUL KHALIQ Gazipur

Anwar Kamal Hootwala

NUSRAT ali Ch. 77 M

now i want distinct number of farmers in every town.. i have 25 million records and i found distinct number of farmers with a query and its returning me 899. now issue is this query is returning me 899 against every town name. like in this query, output should be

town name farmers in each town

Ch. 5-M 1

Ch. 77 M 2

Gazipur 1

Hootwala 2

instead of giving this result, its giving me total number of farmers against each town, like in this scenario, output which i am getting is

town name farmers in each town

Ch. 5-M 6

Ch. 77 M 6

Gazipur 6

Hootwala 6

i hope my question is clear now..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-19 : 21:24:56

quote:
now i want distinct number of farmers in every town.. i have 25 million records and i found distinct number of farmers with a query and its returning me 899. now issue is this query is returning me 899 against every town name. like in this query, output should be
town name farmers in each town

Ch. 5-M 1
Ch. 77 M 2
Gazipur 1
Hootwala 2

The output that you wanted does not coincide with your description. You said "distinct number of farmers", for "Ch. 77 M", the distinct farmer should be 1 but in the output you specified 2.

Based on the output required, what you want is


select town, count (*)
from   farmer_data
group by town

which is your original query that you specified in your first post.

if what you want is "distinct farmer", then Prav4u already shown you the query


select town, count (distinct farmername)
from farmer_data
group by town

and the output for that should be


Ch. 5-M 1
Ch. 77 M 1
Gazipur 1
Hootwala 2

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources