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, Dec 06 '17 at 02:33
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, Dec 06 '17 at 02:37

score 0 · Accepted Answer · answered Dec 06 '17 at 02:33

0

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.

answered Dec 06 '17 at 02:33

Gordon Linoff

1,242,037
58
646
786

Gordon, I truly appreciate it! Your solution worked. If i might ask, why was the code I was attempting to use failing? A quick search landed me on another stack post explaining how SQL handles "group by" and "distinct" as functionally the same. https://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct In my first and third example I have "select distinct" and "group by", going by the above I would have expected it to fail as well. Thanks again for the solution. – silentbob343 Dec 06 '17 at 02:51
@silentbob343 . . . There is no stand-alone `distinct` keyword. You need `select distinct`. – Gordon Linoff Dec 06 '17 at 03:02

score 0 · Answer 2 · answered Dec 06 '17 at 03:03

0

In select statement only one time you can use distinct.

answered Dec 06 '17 at 03:03

balaiah

24
2

Syntax error when I add an additional field to select statement

2 Answers2