0

I am trying to write a query in Oracle which will return id of the driver who transported the most goods in range of a single goods category.

So far my query is as follows:

SELECT 
    truckset.driver_id, cargo.additional_liecence_id,     
    SUM(order_details.cargo_amount) as cargo_sum 
FROM 
    order_details
INNER JOIN 
    truckset on truckset.order_id = order_details.order_id
INNER JOIN 
    cargo on order_details.cargo_id=cargo.id
WHERE 
    cargo.additional_liecence_id IS NOT NULL
GROUP BY 
    truckset.driver_id, cargo.additional_liecence_id
ORDER BY 
    SUM(order_details.cargo_amount) DESC;

And it returns:

| DRIVER_ID | ADDITIONAL_LICENCE_ID | CARGO_SUM |
| 14        | 8                     | 174       |
| 17        | 8                     | 144       |
| 7         | 5                     | 70        |
| 11        | 5                     | 50        |
| 7         | 6                     | 50        |

while I expect something like this:

| DRIVER_ID | ADDITIONAL_LICENCE_ID | CARGO_SUM |
| 14        | 8                     | 174       |
| 7         | 5                     | 70        |
| 7         | 6                     | 50        |
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mmich
  • 101
  • 1
  • 8
  • @ETL_Devs The OP is using `SUM` to find the total for each `driver_id` and `additional_license_id` `GROUP` and then wants to find the row for the `additional_license_id` `GROUP` which has the maximum `SUM(cargo_amount)` (but could have any `driver_id`). – MT0 May 22 '21 at 19:21

1 Answers1

0

I don't have your database, so the syntax may not be exact, but you want to use SQL Analytics functions. You want to do something like this, where you can tweak the syntax in a live database:

WITH T AS (
SELECT truckset.driver_id, cargo.additional_liecence_id, SUM(order_details.cargo_amount) as cargo_sum,
       ROW_NUMBER() OVER (
           PARTITION BY driver_id, additinoal_liecence_id
           ORDER BY cargo_sum
       ) AS ROW_NUMBER -- ADD THIS 'COLUMN' TO YOUR SELECT CLAUSE.
  FROM order_details
 INNER JOIN truckset on truckset.order_id = order_details.order_id
 INNER JOIN cargo on order_details.cargo_id=cargo.id
 WHERE cargo.additional_liecence_id IS NOT NULL
 GROUP BY truckset.driver_id, cargo.additional_liecence_id
 ORDER BY SUM(order_details.cargo_amount) DESC
)
SELECT * FROM T WHERE ROW_NUMBER = 1;

I wrote a whole page on SQL Analytics functions from the Oracle documentation when I taught them in graduate school. It is here:

http://www.qa76.net/sql_analytics

It is very nicely colorized, if I do say so myself :), which makes it much more readable then the original Oracle documentation from which it comes. About two thirds of the way down the page it discusses the ROW_NUMBER() function.

There's probably an even more elegant and expressive way to do this with different SQL Analytics functions, but this should get you started.

Enjoy!

(Edit: My thanks to 'MT0' in the comments for addressing the syntax issue. Changed it to use 'WITH' and filter on that.)

James Madison
  • 845
  • 1
  • 7
  • 18
  • This will not work as you need to nest the query in a sub-query and then put the filter into the outer query; you cannot use the alias directly in the `WHERE` clause as when you declare it in the `SELECT` list. – MT0 May 22 '21 at 19:01
  • MT0, Thanks for the improvement! Made changes inline. – James Madison May 22 '21 at 19:13
  • a simple max(col) over (partition by add...) will solve this , why you are doing a sum – dev May 22 '21 at 19:14
  • 1
    @ETL_Devs The OP was using `SUM` in the question to find the total for the group; it does appear to be necessary to find the total. However, the `ORDER BY` in the `ROW_NUMBER` should be `SUM(order_details.cargo_amount) DESC` rather than trying to use the alias declared in the same level of the query. – MT0 May 22 '21 at 19:18
  • ah , saw it now . – dev May 22 '21 at 19:20
  • Hi thanks for the answer but can you explain to me what it really changes? After executing the query, it returned to me exactly the same table with an additional column where only 1s were, the number of returned rows has not changed compared to my version. – mmich May 23 '21 at 08:02