How to Select and Order By columns not in Groupy By SQL statement – Oracle

I have the following statement:

SELECT  
    IMPORTID,Region,RefObligor,SUM(NOTIONAL) AS SUM_NOTIONAL
From 
    Positions
Where
    ID = :importID
GROUP BY 
    IMPORTID, Region,RefObligor
Order BY 
    IMPORTID, Region,RefObligor

There exists some extra columns in table Positions that I want as output for “display data” but I don’t want in the group by statement.

These are Site, Desk

Final output would have the following columns:

IMPORTID,Region,Site,Desk,RefObligor,SUM(NOTIONAL) AS SUM_NOTIONAL

Ideally I’d want the data sorted like:

Order BY 
    IMPORTID,Region,Site,Desk,RefObligor

How to achieve this?

Answer

It does not make sense to include columns that are not part of the GROUP BY clause. Consider if you have a MIN(X), MAX(Y) in the SELECT clause, which row should other columns (not grouped) come from?

If your Oracle version is recent enough, you can use SUM – OVER() to show the SUM (grouped) against every data row.

SELECT  
    IMPORTID,Site,Desk,Region,RefObligor,
    SUM(NOTIONAL) OVER(PARTITION BY IMPORTID, Region,RefObligor) AS SUM_NOTIONAL
From 
    Positions
Where
    ID = :importID
Order BY 
    IMPORTID,Region,Site,Desk,RefObligor

Alternatively, you need to make an aggregate out of the Site, Desk columns

SELECT  
    IMPORTID,Region,Min(Site) Site, Min(Desk) Desk,RefObligor,SUM(NOTIONAL) AS SUM_NOTIONAL
From 
    Positions
Where
    ID = :importID
GROUP BY 
    IMPORTID, Region,RefObligor
Order BY 
    IMPORTID, Region,Min(Site),Min(Desk),RefObligor