calculate difference between two fields?

[viveleroi0]

I have a database with about 15000 rows, each row has an area code (from telephone numbers).

I want to

SELECT DISTINCT areacode, COUNT(*) FROM table WHERE DATE="$date1"
GROUP BY areacode ORDER BY count DESC

Which will show all distinct area codes, and their counts from highest count to lowest.

But, I need to find a way a combine that with:

SELECT DISTINCT areacode, COUNT(*) FROM table WHERE DATE="$date2"
GROUP BY areacode ORDER BY count DESC

The difference is the two different dates. For each area code, I want to divide the count from the first query by the count from the second query.

I cannot think of a way to do this without using very complicated and inefficient arrays.

There will be about 300 rows returned of DISTINCT area codes. Any ideas?

[tomhath]

Since you are grouping by areacode you don't need the DISTINCT, although it doesn't hurt to have it there.

Also, I wouldn't use DATE as the name of a column, that word is usually reserved.

I usually do this with a SUM and CASE, then do the division in php when you fetch the row. This is untested.:

SELECT areacode, 
  SUM(case when DATE="$date1" then 1 else 0 end) as countD1,
  SUM(case when DATE="$date2" then 1 else 0 end) as countD2
FROM table WHERE DATE in ('$date1','$date2')
GROUP BY areacode 
ORDER BY countD1/countD2 DESC