CODEWITHSUNDEEP
phpmysql
Chadwick Nagka-Gahi Comeros
Chadwick Nagka-Gahi Comeros

Reputation: 5

i want to have the highest number of town_city each year MYsql

i have these on my data base

year | city
2011 | new york
2010 | LA
2010 | LA
2010 | new york
2012 | texas

i have a 2 column contains named year and town_city

and i want to have the highest number of town_city each year .

outout should be

year | city | total
2011 | new york | 1
2010 | LA | 2
2012 | texas | 1

Upvotes: 0

Views: 70

Answers (4)

AshBrad
AshBrad

Reputation: 492

A few steps to do this:

First - you'll want to find all distinct names:

  SELECT DISTINCT column_name(s)
  FROM table_name

This will produce an array that includes New York, LA, Texas - save this as $unique;

Secondly, step through your array, running a SQL query with that specific value, saving the resulting number as a multidimensional array:

  foreach ($unique as Su):
   $u[$i['city'] = $u;
   $u[$i]['num'] =
  //SQL COUNT

   SELECT COUNT(city) AS City FROM TableName
   WHERE City=$u        
   FROM table_name 

 //increase array count
    $i++; 
 endforeach;

This will leave you with an array something like: 

     $u[0]['city'] = New York
     $u[0]['num'] = 2

Upvotes: 1

Aleks G
Aleks G

Reputation: 57316

What you can get your desired result using this:

SELECT year, city, count(*)
FROM city_table ct1
WHERE year=(SELECT max(year) FROM city_table ct2 WHERE ct2.city=ct1.city)
GROUP BY year, city

Upvotes: 0

thebeuving
thebeuving

Reputation: 1

You will want to use this query:

SELECT year,city,count(*) AS total FROM tablename group by city;

group by will only return rows that have a unique city, while the count will show you how many rows were combine.

Upvotes: 0

Philip Sheard
Philip Sheard

Reputation: 5825

SELECT year, city, count(*)
FROM city_table
GROUP by year, city;

Upvotes: 0

Related Questions