Geek Answers Handbook

SQL GROUP BY on the second query

I have a query that will return results from 2 tables in 1 using UNION ALL, and it all works the way I need. However, I need to run GROUP BY and ORDER BY in the returned dataset, but I have a lot of errors and I'm not sure how to solve it.

Here is my request:

SELECT ProductID, Quantity FROM BasketItems UNION ALL SELECT ProductID, Quantity FROM OrderItems

This will return a result set, for example:

 ProductID Quantity 15 2 20 2 15 1 8 5 5 1

Then I want to run GROUP BY in the ProductID field, and then finally ORDER BY DESC in the Quantity field. Thus, in the final result, this particular set of results will ultimately lead to the following:

 ProductID 8 15 20 5

Then I can run queries in this result set, as I usually do

EDIT:

As stated above, but perhaps it’s not enough implied that I will need to run queries on the returned results that do not work, since you cannot run a query on a set of results that have an ORDER BY clause (so far as I have compiled from the list errors)

If you need more information about the problem, here's what:

From this result set I want to get products from the table of products that they relate to

 SELECT * FROM Products WHERE ID IN ( SELECT ProductID FROM ( SELECT ProductID, Quantity FROM BasketItems UNION ALL SELECT ProductID, Quantity FROM OrderItems ) v GROUP BY ProductID ORDER BY SUM(Quantity) DESC )

However, I get this error: the ORDER BY clause is not valid in views, built-in functions, views, subqueries, and common table expressions unless TOP, OFFSET, or FOR XML is also specified.

The output of the products should be in the order in which they are returned in the subquery (by quantity)

+6
source share
4 answers
 SELECT Products.* FROM Products INNER JOIN ( SELECT ProductID, Sum(Quantity) as QuantitySum from ( SELECT ProductID, Quantity FROM BasketItems UNION ALL SELECT ProductID, Quantity FROM OrderItems ) v GROUP BY ProductID ) ProductTotals ON Products.ID = ProductTotals.ProductID ORDER BY QuantitySum DESC
+12
source

will it work?

 SELECT ProductID from ( SELECT ProductID, Quantity FROM BasketItems UNION ALL SELECT ProductID, Quantity FROM OrderItems ) temp GROUP BY temp.ProductID ORDER BY SUM(temp.Quantity) desc
+1
source

Here is the cte version (no live test, so please excuse the errors)

EDIT

 ;WITH myInitialdata_cte(ProductID,Quantity) AS ( SELECT ProductID, Quantity FROM BasketItems UNION ALL SELECT ProductID, Quantity FROM OrderItems ) SELECT b.ID FROM myInitialdata_cte a INNER JOIN Products b ON a.ProductID = b.ID GROUP BY ProductID ORDER BY SUM(a.Quantity) DESC
0
source

I need the following solution. Please help me, one group does not provide a solution. What should I do? enter image description here

0
source

More articles:


All Articles