Group rows by date with count and enumerate rows in the resultset

Question

i have a data in a table, column id and column date

id  || date
1   || 2013-04-18 15:12:32.000  
2   || 2013-04-18 15:13:02.000  
3   || 2013-05-01 19:09:28.000  
4   || 2013-05-01 19:09:46.000  
5   || 2013-05-01 19:10:01.000  
6   || 2013-05-01 19:10:17.000  
7   || 2013-05-02 08:34:54.000  
8   || 2013-05-02 08:35:12.000  
9   || 2013-05-18 15:12:32.000   
10  || 2013-05-18 15:13:02.000

how query to show only one day with count?or one row with count. so the result is.

    || date                    ||
1   || 2013-04-18 15:12:32.000 ||  2
2   || 2013-05-01 19:09:28.000 ||  4
3   || 2013-05-02 08:34:54.000 ||  2
4   || 2013-05-18 15:13:02.000 ||  2

i try in mysql and it works , there is my query.

SELECT id, GROUP_CONCAT( date ) AS date, COUNT( id ) AS count
FROM petak_tetap
GROUP BY DATE( date )

but when i try to sql server 2008, this query error

here is the error message

Msg 195, Level 15, State 10, Line 1
'GROUP_CONCAT' is not a recognized built-in function name.

how to fix that?

There are [already **over two hundred** questions (and answers!)](http://stackoverflow.com/search?q=sql+server+group_concat) for that topic here on this site.... just pick one .... — marc_s, May 30 '13 at 04:24

peterm · Accepted Answer · 2013-05-31T04:11:31.877

3

First of all there is no GROUP_CONCAT function in SQL Server but more importantly you don't need in your case the functionality this function provides. Use aggregate function MIN() or MAX() to get one datetime value per group.

SELECT ROW_NUMBER () OVER (ORDER BY [date]) No, [date], [count]
FROM 
(SELECT MIN([date]) 'date', 
        COUNT(*) 'count'
   FROM petak_tetap
  GROUP BY CAST([date] AS DATE)
) t

Output:

| NO |                         DATE | COUNT |
---------------------------------------------
|  1 | April, 18 2013 15:12:32+0000 |     2 |
|  2 |   May, 01 2013 19:09:28+0000 |     4 |
|  3 |   May, 02 2013 08:34:54+0000 |     2 |
|  4 |   May, 18 2013 15:12:32+0000 |     2 |

Here is SQLFiddle demo

UPDATE In sqlite

SELECT 
 (
   SELECT COUNT(*) 
     FROM 
   ( SELECT 1
       FROM petak_tetap
      WHERE id <= t.id
      GROUP BY DATE(date)
   ) q
 ) No, date, count
  FROM 
 (
   SELECT id, MIN(date) date, COUNT(*) count
     FROM petak_tetap
    GROUP BY DATE(date)   
 ) t

Here is SQLFiddle demo

edited May 31 '13 at 04:11

answered May 30 '13 at 04:49

peterm

91,357
15
148
157

how about in sqlite? @peterm – Puja Surya May 30 '13 at 14:31
http://stackoverflow.com/questions/16837958/group-concat-and-how-to-use-row-number-in-sqlite how to fix it? @peterm – Puja Surya May 31 '13 at 00:28
@Alexbelek See updated answer and sqlfiddle. – peterm May 31 '13 at 04:04
@Alexbelek And the answer for another question is here http://stackoverflow.com/a/16849618/1920232 – peterm May 31 '13 at 04:15
yes i see it. thanks for helping me. i think the function `ROW_NUMBER () ` is only work in sql server. CMIIW @peterm – Puja Surya Jun 02 '13 at 16:34

score 0 · Answer 2 · answered May 30 '13 at 04:41

0

Try this...

SELECT ROW_NUMBER () OVER (ORDER BY DATE) SNo, DATE, COUNT(DATE)AS Count FROM Table
GROUP BY DATE

answered May 30 '13 at 04:41

Shahid Iqbal

2,095
8
31
51

score 0 · Answer 3 · answered May 30 '13 at 05:58

0

Try this

select p1.date,
      (select count(p2.date) from petak_tetap p2 where p2.date=p1.date)
from petak_tetap p1 group by p1.date

answered May 30 '13 at 05:58

Bharadwaj

2,535
1
22
35

score 0 · Answer 4 · answered May 30 '13 at 06:19

Try This One With Complete Output In One Query Only..

SELECT ROW_NUMBER() OVER(Order BY Min(IDate)) srno,  Min(IDate) idate, COUNT(*) Cnt
FROM grpcontact
GROUP BY CONVERT(Varchar,IDate,101)

OutPut >>>

------------------------------------
srno | idate                   | Cnt
------------------------------------
1    | 2013-04-18 15:12:32.000 | 2
2    | 2013-05-01 19:09:28.000 | 4
3    | 2013-05-02 08:34:54.000 | 2
4    | 2013-05-18 15:12:32.000 | 2
------------------------------------

Group rows by date with count and enumerate rows in the resultset

4 Answers4