How will you find the ORDER_ID of all the orders having ORDER_TOTAL greater than the average of ORDER_TOTAL in ORDER's DB2 table

DB2DatabaseBig Data Analytics

We can find all the ORDER_ID which are having ORDER_TOTAL greater than the average value of all the ORDER_TOTAL present in the ORDERS table using the sub-query.

For example, if we have below ORDERS table.

ORDER_ID
ORDER_TOTAL
A22345
1867
A62998
5634
A56902
7615
A56911
87960
A56915
132
A56918
80363


Below is the subquery to find out the desired data.

Example

SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS
   WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)

The result of the above query will be as below.

ORDER_ID
ORDER_TOTAL
A22345
87960
A62998
80363
raja
Published on 30-Nov-2020 09:32:16
Advertisements