Forum Stats

  • 3,733,254 Users
  • 2,246,738 Discussions
  • 7,856,636 Comments

Discussions

Get max of sum(col)

450482
450482 Member Posts: 737
edited December 2007 in SQL & PL/SQL
Hi
select col1, sum(col2) as sm
from
(huge complex query)


How can I select the max(sm) along with col1? Other than using the following method:
select col, sum(col2) as sm from (<huge query>) where
sum(col2) = (select max(sm) from <huge complex query>)

Comments

  • rbglossip
    rbglossip Member Posts: 670
    Give this a try (not tested)
    select col1,sm
    from (select col1, sum(col2) as sm
    from (huge complex query)
    group by col1
    order by 2 desc)
    where rownum = 1
  • 450482
    450482 Member Posts: 737
    edited December 2007
    Hi
    Thanks.
    This is a good idea but wont this return a single row? I may have same values for the sum column (that is multiple rows).So I need all rows that will have that max value for sum(col2).

    ID SM
    1 15
    2 15
    3 15
    4 13
    5 10
    Here I need 3 rows where id = 1, 2 & 3.

    Thx!

    Message was edited by:
    b~o~s~t~o~n
  • 611118
    611118 Member Posts: 379
    It depends what you want.

    If you want the sum of col2 for every given col1, it is a standard GROUP BY query:

    SELECT col1, SUM(col2) FROM (huge complex query) GROUP BY col1;

    Of course, if you are using SQL*Plus, there is a non-SQL solution using COMPUTE SUM of Col2 ON Col1

    Or, a sub-query could be used, as the prior replier showed.
  • 611118
    611118 Member Posts: 379
    OK, i actually read your post this time.....

    WITH Sums AS (SELECT Col1, SUM(Col2) SM FROM (huge complex query))
    SELECT Col1, SM FROM Sums WHERE SM = (SELECT MAX(SM) FROM Sums);
  • rbglossip
    rbglossip Member Posts: 670
    I may have same values for the sum column (that is multiple rows)
    I didn't consider that. Analytics will probably do the trick. I'll see if I can work up an example.
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited December 2007
    If I got it right
    select col1,sm
    from (select col1,
    sm,
    rank() over (order by sm desc) r
    from (select col1,
    sum(col2) as sm
    from (huge complex query
    )
    group by col1 -- as rbglossip says
    )
    )
    where r = 1
    Regards

    Etbin

    Didn't see any group by in the OP but makes sense. It's up to the OP anyway.

    Message was edited by: Etbin
    user596003
  • rbglossip
    rbglossip Member Posts: 670
    I was just about to post the same query, just needs a group by on the inner query.
  • 607743
    607743 Member Posts: 17
    I always thought the W3 SQL School's tutorial was easy to read and understand back in the day....

    It goes through many topics.....

    http://www.w3schools.com/sql/default.asp
  • 450482
    450482 Member Posts: 737
    Thanks sooooo much all. It worked.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited December 2007
    select col,sm
    from (select col, sum(col2) as sm,
    max(sum(col2)) over() as maxSm
    from <huge query>
    group by col)
    where sm = maxSm;
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    nice. I like it better than mine more compact & easier to understand (rank being somehow obscure)

    Regards

    Etbin
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    We can use below solution ,too.
    Maybe, below solution is more simple.
    select col,sm
    from (select col, sum(col2) as sm,
    rank() over(order by sum(col2) desc) as Rn
    from <huge query>
    group by by col)
    where Rn = 1;
  • 450482
    450482 Member Posts: 737
    Thanks. It works:)
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    Definitely. Thank you. I guess I was too anxious to reply (one needs - at least at my age - to be proud of his reaction time from time to time ;-) lol). Perhaps it's time to revise my method (as dictated by the daily routine) of writing sql - wrapping subqueries just keeping the answer correct first and simplifying next (provided there is some time left to do it).

    Regards

    Etbin
This discussion has been closed.