CODEWITHSUNDEEP
mysqlsql
user1056466
user1056466

Reputation: 667

Query to find the average of the number of records per group

I have a Table having 2 Columns i.e FarmerName,PesticideUsed and Town.

  1. How can i find the Average Number of Farmers Using A particular Pesticide.
  2. Average Number of Farmers in Each Town

There are 29 unique Pesticides And 8 Unique Towns.

I tried to write the first one i.e 

adapter.SelectCommand = new SqlCommand(
    "select count(FarmerName)/29 as average_count from try", con);

I dont think it is right.

How i am i going to do that....

Upvotes: 0

Views: 136

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

Average number of farmers using a pesticide:

SELECT PesticideUsed ,AVG(num)
FROM (
SElECT PesticideUsed ,COUNT(*) as num
FROM yourTable
GROUP BY PesticideUsed) a
GROUP BY PesticideUsed

Average number of farmers per town: 

SELECT town ,AVG(num)
FROM (
SElECT town ,COUNT(*) as num
FROM yourTable
GROUP BY town) a
GROUP BY  town

Upvotes: 9

user2996414
user2996414

Reputation: 1

try this instead.

adapter.SelectCommand = new SqlCommand( "select AVG(FarmerName) as average_count from try", con);

Upvotes: -2

Related Questions