Results 1 to 12 of 12

Thread: [RESOLVED] SQL Select sorting question

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Resolved [RESOLVED] SQL Select sorting question

    I have a case where I need to get a value for sorting that is based on values from more than one row and am not sure how to approach it.

    For the sake of simplicity lets say we have the fields ID, Item, Style, qty
    There can be multiple rows where the same item and style are present and the same or different qty.
    I need to sort them by the sum of the qty for each item/style but still need to return the individual rows and qty for each row just having them sorted by the total qty for each item/style combo.

    Is this doable and if so how would I be able to do this?

    Thanks

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Select sorting question

    Well, first thing coming to mind would be to read up on the "ROLLUP"-Clause/Function of SQL. This depending on the DBMS you use.
    --> https://www.sqltutorial.org/sql-rollup/
    As for ordering: Try and error? No idea whatsoever
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: SQL Select sorting question

    Well it looks like using another select with a sum() within the main query seems to get the desire result.
    Select id,item,color,qty (select sum(qty) from mytable b where b.item=a.item and b.color=a.color) as Totalqty from mytable a order by Totalqty

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Select sorting question

    Urghhh....don't like that
    because for every Detail-Set you get the SumTotal in each line
    you'd get something like this

    apple,blue,200,1000
    apple,blue,500,1000
    apple,blue,300,1000

    I'd rather Try something like (untested)
    Code:
    SELECT 
    ID, 
    COALESCE(item,CONCAT('SumTotal for: ',item)) AS Sumitem,
    COALESCE(color, color) AS SumColor, 
    SUM(qty) AS QTY 
    FROM myTable GROUP BY ROLLUP(item,color)
    ORDER BY 
    item,color,qty
    This would return
    apple,blue,200
    apple,blue,300
    apple,blue,500
    SumTotal for: apple,blue,1000
    Last edited by Zvoni; Feb 9th, 2022 at 08:26 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Select sorting question

    Quote Originally Posted by DataMiser View Post
    Well it looks like using another select with a sum() within the main query seems to get the desire result.
    Select id,item,color,qty (select sum(qty) from mytable b where b.item=a.item and b.color=a.color) as Totalqty from mytable a order by Totalqty
    GROUP BY is the simple, old school syntax, for reducing rows by a column(s) and using aggregate functions like SUM() and MAX()

    Code:
    Select id,item,color, sum(qty) "totalqty" from mytable a group by id,item,color
                  order by totalqty
    If the order by is not allowed use "Order by 4"

    In your query, you see the ID, ITEM, COLOR "for every time you have a row", and along with that the SUM() total of that "group"

    Is that really what you want?

    Or just ONE ROW for each ID, ITEM and COLOR?

    btw - in your example - your Sub-query, is NOT using the ID in the WHERE clause. Is that also intentional?

    Zvoni - why suggest ROLLUP? Did the OP ask for totals, sub totals and grand totals along the entire hierarchy?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Select sorting question

    Quote Originally Posted by szlamany View Post
    Zvoni - why suggest ROLLUP? Did the OP ask for totals, sub totals and grand totals along the entire hierarchy?
    From OP
    I need to sort them by the sum of the qty for each item/style but still need to return the individual rows and qty for each row just having them sorted by the total qty for each item/style combo.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Select sorting question

    I need to sort them by the sum of the qty for each item/style but still need to return the individual rows and qty for each row just having them sorted by the total qty for each item/style combo.
    @Zvoni - so his query is correct - he needs the "detail rows" NOT GROUPED BY - they need to be in detail, but sorted by the total quantity for that ITEM/STYLE. Not even including the ID in the join back to the total.

    That is a pretty unique requirement.

    And he nailed it...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Select sorting question

    Quote Originally Posted by DataMiser View Post
    Well it looks like using another select with a sum() within the main query seems to get the desire result.
    Select id,item,color,qty (select sum(qty) from mytable b where b.item=a.item and b.color=a.color) as Totalqty from mytable a order by Totalqty
    If I was doing this - I would usually be in a SPROC anyway, and I would build a TEMP TABLE with ITEM and COLOR and the TOTAL QUANTITY as the three fields. Build that in advance and then JOIN to it.

    But your method will accomplish the same thing.

    You can also join to a DERIVED query, but I'm thinking the execution plans could be the same anyway.

    If you have tons of rows, these are important considerations. If you have just 1000's of rows, SQL will handle this nicely.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Select sorting question

    Ok, i think i see where i tripped: I just can't wrap my mind around that someone could need a result like


    apple,blue,200,1000
    apple,blue,500,1000
    apple,blue,300,1000
    peach,red,400,2500
    peach,red,600,2500
    peach,red,800,2500
    peach,red,700,2500

    sorted by SumTotal per item/style
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: SQL Select sorting question

    Quote Originally Posted by Zvoni View Post
    Ok, i think i see where i tripped: I just can't wrap my mind around that someone could need a result like


    apple,blue,200,1000
    apple,blue,500,1000
    apple,blue,300,1000
    peach,red,400,2500
    peach,red,600,2500
    peach,red,800,2500
    peach,red,700,2500

    sorted by SumTotal per item/style
    Needing to see "detail orders" but based on "total quantity", might help in how they will fulfill said orders. Unique requirement for sure.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: SQL Select sorting question

    Yeah, definitely an odd and unexpected requirement and on top of that it is a Foxpro db that I only have partial access to.

    The calculated total will not be displayed on the users screen just need it for the sorting and yes the reason is because of the way they want to fullfill the orders.

    There is a lot of data in the db but the query should return less than 100 rows.

    The actual query that will be used is far more complex than the sample I posted, I just needed to figure out how to get that part of the sorting done.

    I think that query I came up with will do, at least for now. If speed becomes an issue I will revisit down the road.

    Thanks for the input.
    Last edited by DataMiser; Feb 9th, 2022 at 10:57 AM.

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL Select sorting question

    Quote Originally Posted by DataMiser View Post

    I think that query I came up with will do, at least for now. If speed becomes an issue I will revisit down the road.

    Thanks for the input.
    In that case: the only thing i see (and don't like) is the Sub-Select within the Output-Columns.

    I'd probably go for a (Self-) INNER JOIN
    Untested
    sql Code:
    1. SELECT A.item, A.color, A.qty, B.TotalQty
    2. FROM
    3. MyTable A
    4. INNER JOIN
    5. (SELECT item, color, Sum(Qty) AS TotalQty FROM MyTable GROUP BY item, color) B
    6. ON
    7. B.item=A.Item AND B.color=A.color
    8. ORDER BY
    9. B.TotalQty DESC,
    10. A.Item ASC,
    11. A.Color ASC
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width