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

Question

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?

Solution 1

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

Solution 2

I believe this is

select
  IMPORTID,
  Region,
  Site,
  Desk,
  RefObligor,
  Sum(Sum(Notional)) over (partition by IMPORTID, Region, RefObligor) 
from
  Positions
group by
  IMPORTID, Region, Site, Desk, RefObligor
order by
  IMPORTID, Region, RefObligor, Site, Desk;

... but it's hard to tell without further information and/or test data.

Solution 3

A great blog post that covers this dilemma in detail is here:

http://bernardoamc.github.io/sql/2015/05/04/group-by-non-aggregate-columns/

Here are some snippets of it:

Given:

CREATE TABLE games (
  game_id serial PRIMARY KEY,
  name VARCHAR,
  price BIGINT,
  released_at DATE,
  publisher TEXT
);

INSERT INTO games (name, price, released_at, publisher) VALUES
  ('Metal Slug Defense', 30, '2015-05-01', 'SNK Playmore'),
  ('Project Druid', 20, '2015-05-01', 'shortcircuit'),
  ('Chroma Squad', 40, '2015-04-30', 'Behold Studios'),
  ('Soul Locus', 30, '2015-04-30', 'Fat Loot Games'),
  ('Subterrain', 40, '2015-04-30', 'Pixellore');

SELECT * FROM games;

 game_id |        name        | price | released_at |   publisher
---------+--------------------+-------+-------------+----------------
       1 | Metal Slug Defense |    30 | 2015-05-01  | SNK Playmore
       2 | Project Druid      |    20 | 2015-05-01  | shortcircuit
       3 | Chroma Squad       |    40 | 2015-04-30  | Behold Studios
       4 | Soul Locus         |    30 | 2015-04-30  | Fat Loot Games
       5 | Subterrain         |    40 | 2015-04-30  | Pixellore
(5 rows)

Trying to get something like this:

SELECT released_at, name, publisher, MAX(price) as most_expensive
FROM games
GROUP BY released_at;

But name and publisher are not added due to being ambiguous when aggregating...

Let’s make this clear:

Selecting the MAX(price) does not select the entire row.

The database can’t know and when it can’t give the right answer every time for a given query it should give us an error, and that’s what it does!

Ok… Ok… It’s not so simple, what can we do?

1. Use an inner join to get the additional columns

   SELECT g1.name, g1.publisher, g1.price, g1.released_at
   FROM games AS g1
   INNER JOIN (
     SELECT released_at, MAX(price) as price
     FROM games
     GROUP BY released_at
   ) AS g2
   ON g2.released_at = g1.released_at AND g2.price = g1.price;
   

2. Or Use a left outer join to get the additional columns, and then filter by the NULL of a duplicate column...

   SELECT g1.name, g1.publisher, g1.price, g2.price, g1.released_at
   FROM games AS g1
   LEFT OUTER JOIN games AS g2
   ON g1.released_at = g2.released_at AND g1.price < g2.price
   WHERE g2.price IS NULL;
   

Hope that helps.

Comments

[m3ntat]

You are right on reflection and working through this piece of work he doesn't make sense to include columns not in the group by. Thanks for the info about the Over Partition I didn't know this, very cool!

[Kulbhushan Singh]

Hey suppose if i have create_dt and type_of_order in my table, now i want my different type of orders created on different dates. how can i achieve it?