-
Feb 8th, 2022, 06:19 PM
#1
[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
-
Feb 9th, 2022, 03:20 AM
#2
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
-
Feb 9th, 2022, 08:06 AM
#3
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
-
Feb 9th, 2022, 08:22 AM
#4
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
-
Feb 9th, 2022, 09:06 AM
#5
Re: SQL Select sorting question
Originally Posted by DataMiser
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?
-
Feb 9th, 2022, 09:43 AM
#6
Re: SQL Select sorting question
Originally Posted by szlamany
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
-
Feb 9th, 2022, 10:09 AM
#7
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...
-
Feb 9th, 2022, 10:11 AM
#8
Re: SQL Select sorting question
Originally Posted by DataMiser
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.
-
Feb 9th, 2022, 10:12 AM
#9
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
-
Feb 9th, 2022, 10:13 AM
#10
Re: SQL Select sorting question
Originally Posted by Zvoni
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.
-
Feb 9th, 2022, 10:51 AM
#11
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.
-
Feb 9th, 2022, 11:16 AM
#12
Re: SQL Select sorting question
Originally Posted by DataMiser
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:
SELECT A.item, A.color, A.qty, B.TotalQty FROM MyTable A INNER JOIN (SELECT item, color, Sum(Qty) AS TotalQty FROM MyTable GROUP BY item, color) B ON B.item=A.Item AND B.color=A.color ORDER BY B.TotalQty DESC, A.Item ASC, 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|