How to Select and Order by Columns Not in Groupy by SQL Statement - Oracle

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

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

How to get other columns with are not in GROUP BY clause in a oracle select sql?

Try with analytic functions and subquery

select movie_id, movie_title, category, sales_amt 
from (
  select movie_id, movie_title, category, sales_amt, 
  row_number() over (partition by category order by sales_amt desc) r 
  from movie
) where r = 1

Oracle SQL select rows that are not in GROUP BY clause

You can use analytical functions:

SELECT first_name, second_name, score, COUNT(*)  over (partition by score) FROM tab ;

Select column not on a group by statement

select year, max(round) as max_round, 
       max(raceid) keep (dense_rank last order by round) as raceid
from   races
group  by year
order  by year
;

Documentation for first and last function:

https://docs.oracle.com/database/121/SQLRF/functions074.htm#SQLRF00641

Problems using GROUP BY and ORDER BY in SQL Oracle

You need to order by the aggregate function. ...
Try this

SELECT manager_id AS "Manager ID",
MIN(salary) AS "Lowest Paid Salary"
FROM employees
WHERE manager_id IS NOT NULL
AND salary > 6000 
GROUP BY manager_id
ORDER BY min(salary) DESC;

Keep Column which is not in aggregate function in group by statement

I would suggest this straight forward way and translate your description "outputs the row with the MAX(amount) per customer and all of its values" to SQL:

select * from a_table t
where (t.customerid,t.amount) in (
   select customerid,max(amount) from a_table group by customerid);

Let me know if you need more input.

SQL: How to display a column not in group by expression?

If what you want is a list of Customer Names ordered by the sum of their orders then just change your query from

select O.ONO
...
group by O.ONO

to

select C.CName
...
group by C.CName

This will display the customer name and the order number. NOTE: This will repeat the customer name as many times as there are orders for that customer

Also, my preferred style of SQL queries is

select C.CName
from Customers C
    INNER JOIN Orders O ON C.CNO = O.CNO
    INNER JOIN Odetails OD ON O.ONO = OD.ONO
    INNER JOIN Parts P ON OID.PNO = P.PNO
where P.PNO=Od.PNO and Od.ONO=O.ONO and O.CNO=C.CNO
group by C.CName
order by sum(Od.QTY*P.PRICE)desc;

An additional suggestion to clarify your code. (please note: I'm not an oracle person), in my company (SqlServer) generally you would have the table named after the entity, with the primary key being just Id, and then any property named without a prefix. For instance does OID refer to OrderId or OfficeId?

EG:

Customer:

• Id
• Name

Order:

• Id
• CustomerId

Then your queries are much more readable:

SELECT Name
FROM Customer
    INNER JOIN Order ON Customer.Id = Order.CustomerId
WHERE ...
ORDER BY Customer.Name

---

Related Topics