Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL GROUP BY on a sub query

I have a query that will return results from 2 tables into 1 using a UNION ALL, which all works as I need it to. However I need to run a GROUP BY and an ORDER BY on the returned dataset however I am getting many errors and I'm not sure how to solve it.

Here is my Query:

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

This will return a results set such as this:

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

I then want to run a GROUP BY on the ProductID field and then finally an ORDER BY DESC on the Quantity field. So in the final output, this particular results set will finally result in this:

ProductID
8
15
20
5

I can then run queries on this result set as I usually do

EDIT:

As stated above, but maybe not implied enough is that I will need to run queries on the returned results, which isn't working as you cannot run a query on a set of results that have an ORDER BY clause (so far as I gathered from the error list)

If you want more information on the problem, here it is:

From this results set, I want to get the products from the product table 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 invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

The output of products need to be in the order that they are returned in the sub query (By quantity)

like image 536
JakeJ Avatar asked Aug 08 '12 10:08

JakeJ


People also ask

Can you do a GROUP BY in a subquery?

You can use group by in a subquery, but your syntax is off.

Is an ORDER BY clause allowed in a subquery?

In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY ... LIMIT ... changes the result, the set of rows, not only their order).

Can we use GROUP BY in subquery Oracle?

A subquery cannot be placed in the Oracle GROUP BY Clause. Sub-queries can be divided into two main categories : Single Row Subqueries – subqueries that return zero or one row to the outer SQL statement. Multiple Row Subqueries – subqueries that return more than one row to the outer SQL statement.

Can we use sub queries in the HAVING clause to filter out groups of records?

You can use sub queries in you SQL's HAVING clause to filter out groups of records. Just as the WHERE clause is used to filter rows of records, the HAVING clause is used to filter groups.


2 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
like image 183
podiluska Avatar answered Oct 21 '22 12:10

podiluska


will this 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
like image 21
NG. Avatar answered Oct 21 '22 14:10

NG.