CODEWITHSUNDEEP
sqlgroup-byoracle-sqldeveloper
Rish
Rish

Reputation: 693

SQL: Group by statement showing same values?

I have this SQL code:

select count(req_id), SUBMITS from requests where result='REJECT' GROUP BY SUBMITS

The output I'm getting is: 

1    16-JUN-17
1    19-JUN-17
1    16-JUN-17
1    16-JUN-17

whereas I want:

3    16-JUN-2017
1    19-JUN-2017

Upvotes: 1

Views: 67

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You probably have hours, minutes, seconds in your SUBMITS column that your tool is not showing you; this way, you believe that all that rows have the same SUBMITS value, but they only have the same date, but different time.

If so, you only need to limit the informations to a date, without hours, minutes and seconds:

select count(req_id), trunc(SUBMITS)
from requests
where result='REJECT'
GROUP BY trunc(SUBMITS)

For example:

SQL> create table requests (submits date, result VARCHAR2(10), req_id NUMBER);

Table created.

SQL> insert into requests (req_id, submits, result) values (1, sysdate, 'REJECT');

1 row created.

SQL> insert into requests (req_id, submits, result) values (2, sysdate, 'REJECT');

1 row created.

SQL> insert into requests (req_id, submits, result) values (3, sysdate, 'REJECT');

1 row created.    

SQL> select count(req_id), trunc(SUBMITS)
  2  from requests
  3  where result='REJECT' 
  4  GROUP BY trunc(SUBMITS);

COUNT(REQ_ID) TRUNC(SUB
------------- ---------
            3 21-JUN-17

SQL> select count(req_id), SUBMITS
  2  from requests
  3  where result='REJECT'
  4  GROUP BY SUBMITS;

COUNT(REQ_ID) SUBMITS
------------- ---------
            1 21-JUN-17
            1 21-JUN-17
            1 21-JUN-17

Upvotes: 2

Related Questions