CODEWITHSUNDEEP
sqlmysqlconditional-statementscase
UserBSS1
UserBSS1

Reputation: 2211

Conditional Group By CASE statement

Consider the Table 

 count | lat | lng | Date
 ------+-----+-----+------
   1   | 10  | 21  | 01/12
   1   | 10  | 21  | 01/12
   1   | 20  | 25  | 02/12
   1   | 30  | 31  | 02/12
   1   | 30  | 31  | 02/12
   1   | 10  | 21  | 03/12

The above table contains latitude/longitude by date. I want to group the lat/lng over a date

e.g. for date 01/12, the count will become 2 for lat/lng 10/21, then for the next record(3rd) it will be 1, and for 4th & 5th, it will become 2 for the date 02/12.

 count | lat | lng | Date
 ------+-----+-----+------
   2   | 10  | 21  | 01/12
   1   | 20  | 25  | 02/12
   2   | 30  | 31  | 02/12
   1   | 10  | 21  | 03/12

I can neither group by 'date', nor by 'lat/lng' combination. Only a conditional group by statement can solve it (maybe) otherwise I have to divide & conquer! Anyone has the solution for such statement?

using MySQL 5

Upvotes: 2

Views: 896

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332581

You'll have to provide information -- table(s) involved (including column data types) -- for us to know why the following doesn't work for you:

  SELECT COUNT(*) AS count,
         t.lat,
         t.lng,
         t.date
    FROM YOUR_TABLE t
GROUP BY t.lat, t.lng, t.date

Upvotes: 3

Related Questions