Group_concat And How To Use Row Number In Sqlite

i have a data in a table, column id and column date id || datetime 1 || 2013-05-24 19:23:16 2 || 2013-05-28 19:24:20 3 || 2013-05-28 19:25:05 4 || 2013-05-30 19:25:39

Solution 1:

There is no need for GROUP_CONCAT() functionality in your case. Your last query in the question is for SQL Server. Sqlite doesn't have implementation for ROW_NUMBER().

This being said, try

SELECT 
 (
   SELECTCOUNT(*) 
     FROM 
   ( SELECT1FROM dataPetak
      WHERE id <= t.id
      GROUPBYDATE(datetime)
   ) q
 ) No, datetime, count
  FROM 
 (
   SELECT id, MIN(datetime) datetime, COUNT(*) count
     FROM dataPetak
    GROUPBYDATE(datetime)   
 ) t

Output:

| No |            datetime | count |
------------------------------------
|  1 | 2013-05-24 19:23:16 |     1 |
|  2 | 2013-05-28 19:24:20 |     2 |
|  3 | 2013-05-30 19:25:39 |     2 |

Here is SQLFiddle demo

Solution 2:

Try this query

selectdate(datetime) asdate,  count(*) as count from tbl 

groupbydate(datetime)

FIDDLE

|       date | count |
----------------------
| 2013-05-24 |     1 |
| 2013-05-28 |     2 |
| 2013-05-30 |     2 |