CODEWITHSUNDEEP
mysqlsqloracle11g
AkiShankar
AkiShankar

Reputation: 332

Need to convert MySQL group by query to oracle

How can I convert this query to Oracle group By. I am facing the issue when migrating the application from MySQL to Oracle database.

SELECT j.id                   AS j__id
,      j.processing_office_id AS j__processing_office_id
,      j.lga_id               AS j__lga_id
,      j.created_at           AS j__created_at
,      j.updated_at           AS j__updated_at
,      j.created_by           AS j__created_by
,      j.updated_by           AS j__updated_by
,      j.deleted_at           AS j__deleted_at
,      t.name                 AS t__0
,      t3.id                  AS t3__1
,      t3.name                AS t3__2 
FROM   join_processing_office_state_lga j 
LEFT 
JOIN   tbl_lga t 
ON     j.lga_id = t.id 
AND    t.deleted_at IS NULL
AND    t.active_flag = 'Y'
LEFT 
JOIN   tbl_procesing_office t2
ON     j.processing_office_id = t2.id
LEFT 
JOIN   tbl_state t3 
ON     t.state_id = t3.id
AND    t3.deleted_at IS NULL
AND    t3.active_flag = 'Y'
WHERE  t2.active_flag = 'Y' 
AND    t2.active_flag = 'Y' 
AND    j.deleted_at IS NULL
GROUP 
BY     t.state_id
ORDER 
BY     t3.name

Upvotes: 1

Views: 352

Answers (1)

Guido Leenders
Guido Leenders

Reputation: 4262

Your query uses a group by on one column. Different from some other platforms such as Sybase, Oracle requires all individual select elements to occur in the group by or be used in a group function such as sum, max and avg. I consider this requirement a good thing; it makes the outcome of queries deterministic. To port this query to Oracle, ensure for each select item you either:

  • include it in the group by (nowadays that can be done using the alias or the full expression).
  • or include it in a group function such as max.

If you need subtotals etc. please consider the use of analytical functions. They allow you to specify a different window for which something is calculated. Or use grouping sets and the likes. Good luck!

Upvotes: 1

Related Questions