This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Is there GROUP BY operator in SuiteQL? when I tried it, it shows Invalid or unsupported search error

Either add all the columns you have in select statements in the group by.
Or use some aggregate function like min/max in front of all the select columns.

Select
Max(t.id),
t.entity
From
Transaction as t
Group by 
t.entity

I have added all columns but I'm using JOIN statement as well and I think that causes any issue?

<@U02S7MW41QT> Here's a good jumping off point for SuiteQL reference. It'd be good to familiarize yourself with Oracle SQL / SQL-92 syntax and the specific implementation that SuiteQL uses - <https://system.netsuite.com/app/help/helpcenter.nl?fid=section_156257770590.html>

```SELECT
	sje.tranid,
	sje.trandate,
	jeline.cseg_cis_resource,
	jeline.debit,
	jeline.memo,
sje.id
FROM
	StatisticalJournalEntry as sje
JOIN StatisticalJournalEntryLine as jeline on sje.id = jeline.journal
WHERE 	jeline.cseg_cis_resource = 8 GROUP BY sje.id```

Try adding max() in front of all the columns

Some aggregate function needs to happen for all the columns you have in select statement
Either it's grouping, max, min, avg, count, etc.

<@U02S7MW41QT> When raghav says to add all the columns, he means that you must add all columns that aren't using a function like SUM(), AVG() etc, to GROUP BY

If you don’t want aggregated data and instead you just want to remove duplicates, instead of GROUP BY you can use DISTINCT

```SELECT DISTINCT
	sje.tranid,
	sje.trandate,
	jeline.cseg_cis_resource,
	jeline.debit,
	jeline.memo,
sje.id
FROM
	StatisticalJournalEntry as sje
JOIN StatisticalJournalEntryLine as jeline on sje.id = jeline.journal
WHERE 	jeline.cseg_cis_resource = 8```

Thank you. I want to get the number of lines per every JEs