Syntax error when I add an additional field to select statement

Question

SQL novice hoping to get some help with a select statement

I can run this successfully:

select distinct t.QUALIFIEDORGUNITCODE as DEALERNUMBER, 
       count(distinct(t.TRANSACTIONID)) as TRANSACTION_VOLUME
  from adtdealers.transaction t
  where t.DATECREATED between '01-oct-17' and '01-nov-17'
  group by t.QUALIFIEDORGUNITCODE;

but if I attempt to add another field to the select I receive the following error:

ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause: *Action: Error at Line: 17 Column: 105

select to_char(t.DATECREATED, 'MON-DD') as DAY,
       count(distinct(t.TRANSACTIONID)) as TRANSACTION_VOLUME,
       distinct t.QUALIFIEDORGUNITCODE as DEALERNUMBER
  from adtdealers.transaction t
  where t.DATECREATED between '01-oct-17' and '01-nov-17'
  group by to_char(t.DATECREATED, 'MON-DD'), t.QUALIFIEDORGUNITCODE;

I can also run this succesfully:

select distinct t.QUALIFIEDORGUNITCODE as DEALERNUMBER,
       to_char(t.DATECREATED, 'MON-DD') as DAY
  from adtdealers.transaction t
  where t.DATECREATED between '01-oct-17' and '01-nov-17'
  group by t.QUALIFIEDORGUNITCODE, t.DATECREATED;

It would help greatly if you explained what a 00936 error is specifically; it's highly unlikely that the only error information is that number. What is the specific error message you're getting? It's on the screen right in front of you, so there's no reason for you not to include it here in your post. — Ken White
Sorry about that. 00936 is an oracle error and the message given is ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause: *Action: Error at Line: 17 Column: 105 — silentbob343

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-12-06T02:33:41

You almost never need select distinct if you are using group by. I think you simply intend:

select to_char(t.DATECREATED, 'MON-DD') as DAY,
       count(distinct t.TRANSACTIONID) as TRANSACTION_VOLUME, 
       t.QUALIFIEDORGUNITCODE as DEALERNUMBER
from adtdealers.transaction t
where t.DATECREATED between date '2017-10-01' and date '2017-11-01'
group by to_char(t.DATECREATED, 'MON-DD'), t.QUALIFIEDORGUNITCODE;

Notice that I changed the date literals to use ISO-standard formats at the date keyword.

Syntax error when I add an additional field to select statement

2 Answers