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 a way to get a saved search that represents the following psuedo-sql?
```SELECT * FROM customer
JOIN (
    SELECT customer.iid AS customer_iid, COUNT(*) AS so_count FROM sales_order
    WHERE sales_order.customer_iid = customer.iid
) AS num_sales_orders ON num_sales_orders.customer_iid = customer.iid
WHERE num_sales_orders.so_count &gt; 1```

Probably better to ask what your search goal is than the code here.

Are you looking for customers who have at least one sales order?

customers who have _more_ than one sales order

which NetSuite support has so far been unable to answer, so if anyone has any ideas, i'm all ears

Make a transaction search 
Criteria: mainline: yes
Summary criteria: count internalid &gt; 1
Result: Name (Grouped), internalid (count)

thanks <@UP0GS40AF>. so instead of doing a search of customers, this is doing a search of transactions and grouping by the associated customer?

Yeah, but you could do it the other way around as well.

this is great, this at least gets me half-way to what I'm trying to do. Summaries are what I was missing.

is there a way to compare two summary counts against each other in the same saved search?

my end goal is
```SELECT installation_site.name, so_count.so_count, est_count.est_count FROM installation_site
JOIN (
    select COUNT(*) as so_count
    FROM transactions
    WHERE transactions.type = "Sales Order" AND transactions.custbody_uv_install_site = installation_site
) as so_count
JOIN
    SELECT COUNT(*) AS est_count
    from transactions
    WHERE transactions.type = "Quote" AND quote.custbody_uv_install_site = installation_site AND quote.status = open
) as est_count
WHERE so_count &gt;= 1 AND est_count &gt;= 1```

filter the transactions on both Sales Orders and Estimates. Have one formula field that only returns 1 on sales orders and another that returns 1 on Estimates.
Have two summary criterias for both your custom formulas.

image.png

thanks for the screnshots, attempting this now

so it looks like that decode under summary is working for me (very few results, but have to check if that actually reflects the data tomorrow). I appreciate the insight as I'm still trying to learn how translate SQL knowledge into NetSuite Saved Search terminology

Make sure you turn on expression builder and use "Or" in order to see ones with more than 1 sales order or more than 1 estimate.

Thanks. I'm using the following settings which are so far giving me the results I was trying to obtain. Definitely appreciate the help with summing / functions within NetSuite.