Sorting mySQL Results

[Hartley]

All right, in this table, there are two different columns with timestamps, going to give them the variables $join and $leave for now. I can select all of them just fine, however, I need to do one sort. I need to see how many of these have a difference of two weeks in the $join and $leave (Or 1209600 difference in the timestamps).

 

I simply need a count of of them. Is there a way to construct this query to get what I'm looking for?

[Psycho]

SELECT *, (leave - join) as diff FROM table WHERE diff > 1209600 SORT BY diff

[Hartley]

I get the following error:

 

Unknown column 'diff' in 'where clause'

[Psycho]

Ok, I guess you can refrence a "created" column in the where caluse. Pity. Anyway, I test this and it works:

 

SELECT *, (leave - join) as diff FROM table WHERE (leave - join) > 1209600 SORT BY diff

[Hartley]

It works good so far, but when I throw in a count(id), I get another error. Is there perhaps a way to do the count outside of the mySQL, or with a different syntax, so I can get a count of all the rows that fit this query?

 

Thanks again!

 

Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

[Psycho]

Um, could you please show the complete query you are using?

 

This might be what you are looking for:

 

SELECT COUNT(*) FROM table WHERE (leave - join) > 1209600 GROUP BY id