CODEWITHSUNDEEP
sqloracle-databasegroup-by
Marium
Marium

Reputation: 253

Oracle SQL: How return several distinct columns

I have an Oracle Query that outputs

enter image description here

but I wish to output all distinct column except for Incident ID, i.e.

enter image description here

I tried adding GROUP BY to Oracle Query but get error, 

DataSource.Error: Oracle: ORA-00979: not a GROUP BY expression
Details:
    DataSourceKind=Oracle
    DataSourcePath=dlporacle
    Message=ORA-00979: not a GROUP BY expression
    ErrorCode=-2147467259

Below is my Query ... please assist

SELECT 
   I.INCIDENTID                    AS "Incident ID",
   I.CREATIONDATE                  AS "Creation Date",
   MO.IPADDRESS           AS "IP Address",
   MO.DOMAINUSERNAME               AS "Login ID",
   MO.ENDPOINTMACHINENAME          AS "Computer Name",
   M.MESSAGESUBJECT                AS "Email Subject"
 FROM MESSAGE M,
 JOIN INCIDENT I ON M.MESSAGEID = I.MESSAGEID AND M.MESSAGESOURCE = I.MESSAGESOURCE AND  M.MESSAGEDATE  = I.MESSAGEDATE
 JOIN MESSAGEORIGINATOR MO ON  M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID
 WHERE  M.MESSAGESOURCE = 'ENDPOINT'
    AND I.ISDELETED = 0
  GROUP BY MO.IPADDRESS, MO.DOMAINUSERNAME, MO.ENDPOINTMACHINENAME, M.MESSAGESUBJECT

Upvotes: 1

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Fix the JOIN syntax and use aggregation:

SELECT
   MIN(I.INCIDENTID)      AS "Incident ID",
   I.CREATIONDATE                  AS "Creation Date",
   MO.IPADDRESS           AS "IP Address",
   MO.DOMAINUSERNAME               AS "Login ID",
   MO.ENDPOINTMACHINENAME          AS "Computer Name",
   M.MESSAGESUBJECT                AS "Email Subject"
FROM MESSAGE M JOIN
     INCIDENT I
     ON M.MESSAGEID = I.MESSAGEID AND
        M.MESSAGESOURCE = I.MESSAGESOURCE AND
        M.MESSAGEDATE = I.MESSAGEDATE JOIN
     MESSAGEORIGINATOR MO
     ON  M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID
WHERE M.MESSAGESOURCE = 'ENDPOINT' AND I.ISDELETED = 0
GROUP BY I.CREATIONDATE, MO.IPADDRESS, MO.DOMAINUSERNAME, MO.ENDPOINTMACHINENAME, M.MESSAGESUBJECT

All unaggregated columns need to be in the GROUP BY.

Why do the source and date have to match for the join to INCIDENT as well as the message id? It would seem that the message id should be sufficient.

Upvotes: 1

demircioglu
demircioglu

Reputation: 3465

Try this

 SELECT MIN(I.INCIDENTID)               AS "Incident ID",
        I.CREATIONDATE                  AS "Creation Date",
        MO.IPADDRESS                    AS "IP Address",
        MO.DOMAINUSERNAME               AS "Login ID",
        MO.ENDPOINTMACHINENAME          AS "Computer Name",
        M.MESSAGESUBJECT                AS "Email Subject"
   FROM MESSAGE M,
        INCIDENT I,
        MESSAGEORIGINATOR MO
  WHERE M.MESSAGESOURCE = 'ENDPOINT'
    AND M.MESSAGESOURCE = I.MESSAGESOURCE
    AND M.MESSAGEID = I.MESSAGEID
    AND M.MESSAGEDATE = I.MESSAGEDATE 
    AND M.MESSAGEORIGINATORID = MO.MESSAGEORIGINATORID 
    AND I.ISDELETED = 0
  GROUP BY I.CREATIONDATE, MO.IPADDRESS, MO.DOMAINUSERNAME, MO.ENDPOINTMACHINENAME,M.MESSAGESUBJECT

Upvotes: 1

Related Questions