SQL query describing usage of SUM aggregate function and GROUP-BY with HAVING

DB2DatabaseBig Data AnalyticsSQL

Problem: Write the DB2 SQL query to give the sum of ORDER_TOTAL for the orders placed on 29th July and 30th July individually. The result should come in a single table.

Solution

We can find the sum of ORDER_TOTAL for the orders placed on 29th and 30th July individually using aggregate function SUM, GROUP BY and HAVING.

For example, if we have an ORDER table as below.

ORDER_ID
ORDER_TOTAL
ORDER_DATE
Z22345
342
29-07-2020
Z62998
543
30-07-2020
Z56990
431
12-07-2020
Z56902
6743
29-07-2020
Z99781
443
10-07-2020
Z56112
889
30-07-2020

 

Below is the query which will give the desired result.

Example

SELECT ORDER_DATE, SUM(ORDER_TOTAL) FROM ORDERS
GROUP BY ORDER_DATE
HAVING ORDER_DATE IN (‘29-07-2020’, ‘30-07-2020’)

In this query, we have selected ORDER_DATE and ORDER_TOTAL with aggregate function SUM.

The GROUP BY will ensure that the sum of ORDER_TOTAL is taken date wise and HAVING clause will ensure that the result of only 29th and 30th July is displayed. The query will give the following result.

ORDER_DATE
ORDER_TOTAL
29-07-2020
7085
30-07-2020
1432
raja
Published on 30-Nov-2020 09:34:40
Advertisements