0

I am trying to run the following query:

SELECT TAB.week_end, 
    COUNT(TAB.id)
FROM (
        SELECT C.week_end,              
            C.ID   
        FROM my_table C     
        WHERE C.week_end IS NOT NULL
    ) TAB
GROUP BY TAB.week_end, TAB.id

Expected result:

week_end  id
2016-01   45000
2016-02   61000
2016-03   59000

What I'm getting is: 

week_end  id
2016-01   1
2016-01   1
2016-02   1
2016-02   1

Now I know the difficulties when grouping by date. But in my case the date is already stored as varchar2. Anybody can help me out here?

xcelm
  • 541
  • 1
  • 6
  • 19

2 Answers2

3

Use following:

 SELECT TAB.week_end, COUNT(TAB.id)
 FROM (SELECT C.week_end,              
         C.ID   
 FROM my_table C     
 WHERE C.week_end IS NOT NULL) TAB
 GROUP BY TAB.week_end

If the two tables are the same u can achieve as follows:

 SELECT C.week_end, COUNT(C.id)  
 FROM my_table C     
 WHERE C.week_end IS NOT NULL
 GROUP BY C.week_end
Martin Keown
  • 58
  • 4
0

Remove Tab.id from group by solves it

xcelm
  • 541
  • 1
  • 6
  • 19