SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

Question

I am getting this error "not a GROUP BY expression" while running this code in java but working fine in SQL Developer. Not sure where I am doing wrong. Anyone please help

Error : SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

SELECT data.portfolioid AS "CUSTOMER ID",
       company.name AS "COMPANY",
       trunc(data.startdate, :format) AS "USAGE DATE",
       SUM(data.sessions) AS "SESSIONS",
       SUM(data.zerosessions) AS "ZEROSESSIONS",
       SUM(data.sms) AS "SMS",
       SUM(data.VOICEDURATION) AS "VOICEDURATION",
       SUM(data.totalbytes) AS "TOTAL BYTES",
       SUM(data.billedbytes) AS "NORMALIZED BYTES",
       sim.custom0 AS "CUSTOM1"
FROM
  (SELECT portfolioid,
          context,
          startdate,
          totalbytes,
          billedbytes,
          sessions,
          zerosessions,
          sms,
          voiceduration,
          sim.imsi
   FROM nmma_abc_mo DATA
   INNER JOIN nn_abc sim ON data.imsi = sim.imsi
   WHERE portfolioid IN
       (SELECT pf.id
        FROM nc_vt_abc cb,
             nn_abc pcb,
             nn_abc pf
        WHERE pcb.id = cb.productid
          AND pf.id = pcb.portfolioid
          AND pf.domainid = :domainid
          AND (:portfolioid = -1
               OR pf.id = :portfolioid) )
     AND data.startdate BETWEEN to_date(:startDate, 'YYYY-MM-DD HH24:MI:SS') AND to_date(:endDate, 'YYYY-MM-DD HH24:MI:SS') ) DATA
LEFT JOIN nnp_abc portfolio ON portfolio.id = data.portfolioid
LEFT JOIN nnpc_abc company ON company.id = portfolio.companyid
LEFT JOIN nnsc_v_abc sim ON sim.imsi = data.imsi
WHERE sim.custom0 IS NOT NULL
GROUP BY data.portfolioid,
         company.name,
         trunc(data.startdate, :format),
         sim.custom0
ORDER BY data.portfolioid

Answer 1

So as I mentioned in the comments, try moving your date formatting to the inner query, to avoid the repeated placeholder like this:

SELECT data.portfolioid AS "CUSTOMER ID",
       company.name AS "COMPANY",
       data.usage_date AS "USAGE DATE",
       SUM(data.sessions) AS "SESSIONS",
       SUM(data.zerosessions) AS "ZEROSESSIONS",
       SUM(data.sms) AS "SMS",
       SUM(data.VOICEDURATION) AS "VOICEDURATION",
       SUM(data.totalbytes) AS "TOTAL BYTES",
       SUM(data.billedbytes) AS "NORMALIZED BYTES",
       sim.custom0 AS "CUSTOM1"
FROM   (SELECT portfolioid,
               CONTEXT,
               trunc(startdate, :format) AS USAGE_DATE
               totalbytes,
               billedbytes,
               sessions,
               zerosessions,
               sms,
               voiceduration,
               sim.imsi
        FROM   nmma_abc_mo data
        INNER  JOIN nn_abc sim ON data.imsi = sim.imsi
        WHERE  portfolioid IN (SELECT pf.id
                               FROM   nc_vt_abc cb, nn_abc pcb, nn_abc pf
                               WHERE  pcb.id = cb.productid
                               AND    pf.id = pcb.portfolioid
                               AND    pf.domainid = :domainid
                               AND    (:portfolioid = -1 OR pf.id = :portfolioid))
        AND    data.startdate BETWEEN to_date(:startDate, 'YYYY-MM-DD HH24:MI:SS') AND
               to_date(:endDate, 'YYYY-MM-DD HH24:MI:SS')

        ) data
LEFT   JOIN nnp_abc portfolio ON portfolio.id = data.portfolioid
LEFT   JOIN nnpc_abc company ON company.id = portfolio.companyid
LEFT   JOIN nnsc_v_abc sim ON sim.imsi = data.imsi
WHERE  sim.custom0 IS NOT NULL
GROUP  BY data.portfolioid, company.name, data.usage_date, sim.custom0
ORDER  BY data.portfolioid;