In New to NetSuite | SuiteQL Overview and New to NetSuite | Understanding SuiteQL Syntax articles, we explored the fundamental concepts, capabilities, and structure of SuiteQL.
SuiteQL Syntax for Advanced Queries goes beyond simple selections and joins to deliver robust, flexible, and analytical queries within database. SuiteQL’s capabilities are similar to standard SQL (with some NetSuite-specific constraints). One of these advanced features is COUNT.
The COUNT function in SuiteQL tallies the number of rows (or non-null values of a column) in a result set or group. It’s essential for summarizing activity, transaction counts, record audits, or even deduplication.
Syntax
SELECT COUNT(*) AS total_rows
FROM record
[WHERE ...]
- COUNT(*) counts all rows.
- COUNT(column) counts only non-null values in that column.
For grouped counts:
SELECT group_field, COUNT(*) AS grouped_count
FROM record
[WHERE ...]
GROUP BY group_field
Sample Query 1
SELECT COUNT(*) AS transaction_count
FROM transaction
WHERE type = 'SalesOrd' -- Only count Sales Orders
- Returns the total number of Sales Orders in your account
Sample Query 2
SELECT COUNT(memo) AS transaction_count
FROM transaction
WHERE type = 'SalesOrd' -- Only count Sales Orders
- Returns the number of Sales Orders where the Memo field is not null.
Sample Query using COUNT with GROUP BY
SELECT
type, -- Transaction type (e.g., SalesOrd, CustInvc, CashSale, etc.)
COUNT(*) AS count_per_type
FROM transaction
GROUP BY type
- Returns the total count of transactions for each transaction type in your system.
The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
Stay tuned for upcoming articles on how to use SuiteQL Syntax in SuiteCloud Product Area. Stay updated by following the New to NetSuite > SuiteCloud category to receive notifications whenever new articles are published.
