Example of SQL query describing COUNT function and GROUP BY

DB2DatabaseBig Data AnalyticsSQL

Problem: Write a query on TRANSACTIONS DB2 table to list down the number of orders (ORDER_ID) assigned to a particular transaction (TRANSACTION_ID).

Solution

We can use the below query to find out the number of orders assigned to a particular transaction id on TRANSACTIONS DB2 table.

Example

SELECT TRANSACTION_ID, COUNT(ORDER_ID) FROM TRANSACTIONS
   GROUP BY TRANSACTION_ID

We will use GROUP BY function on the ORDER_ID to fetch the result order wise. The COUNT function will count the number of orders. For example, we have below DB2 ORDERS table.

TRANSACTION_ID
ORDER_ID
IRN22345
A23118
IRN22345
A45901
IRN22345
A67990
IRN56902
A23119
IRN99781
A67921
IRN56902
A23167

 

The result of our DB2 query will return the below result.

TRANSACTION_ID
COUNT(ORDER_ID)
IRN22345
3
IRN56902
2
IRN99781
1
raja
Published on 01-Dec-2020 09:19:33
Advertisements