SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Number of distinct farmers in each town
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nimxoo
Starting Member

Pakistan
5 Posts

Posted - 11/17/2013 :  10:08:23  Show Profile  Reply with Quote
-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

India
15 Posts

Posted - 11/17/2013 :  10:48:31  Show Profile  Reply with Quote
try this
select town, count (distinct farmername)
from farmer_data
group by town

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

Edited by - Prav4u on 11/17/2013 10:49:13
Go to Top of Page

nimxoo
Starting Member

Pakistan
5 Posts

Posted - 11/17/2013 :  12:46:05  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 11/17/2013 :  22:50:21  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 11/18/2013 :  05:53:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

nimxoo
Starting Member

Pakistan
5 Posts

Posted - 11/19/2013 :  15:37:40  Show Profile  Reply with Quote
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..
Go to Top of Page

NguyenL71
Posting Yak Master

USA
208 Posts

Posted - 11/19/2013 :  16:48:48  Show Profile  Reply with Quote
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..

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 11/19/2013 :  21:24:56  Show Profile  Reply with Quote
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
Time is always against us


Edited by - khtan on 11/19/2013 21:27:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000