0

Group By without getting raw data

Joe Enos

February 26, 2023 17:22

None

Follow

I'm looking for the Jet equivalent to a GROUP BY. A simple example:

SELECT [Vendor No_], COUNT(1) AS [Num Items]
FROM [dbo].[Item] 
GROUP BY [Vendor No_]

In Jet, I can do:
E5:
=NL("Rows","Item","Vendor No.","+Vendor No.","<>@@")
F5:
=NL("Count","Item",,"Vendor No.",$E5)

But that actually runs a single SQL query for every line. Not so bad when there are only a few hundred, but if there are thousands or tens of thousands of groups, it's slow for both the Excel side and also the SQL side.

From what I can tell, I can do this with grouping and subtotaling, but it needs all the raw data upfront, which is not great when there are hundreds of thousands of actual records.

Is there a way to group in advance in a single statement like you can in SQL?

• Facebook
• Twitter
• LinkedIn

2 comments

• 1
  
  CascadiaDataworks February 27, 2023 18:29

  So yes and no.  Those with a heavy SQL background often struggle with Jet, even though it is actually creating SQL queries.  In this case, you are creating two different queries with Jet but there is no way to take both and combine into one statement.  Each cell is a single statement, it's just how Jet is designed to work and allows non-SQL folk to build reports.  What Jet does in the background is to try and batch the query and pull the data it needs in as few queries as possible so in that sense, it is almost like creating one query.

  That said, I just did a query against a table with 2000 entities and then counted all the G/L Entry transactions associated with that entity and it took 1:07 against a very slow cloud BC data source.  Against my on-prem source, it took 12 seconds.  

  It goes to show that your data source can have a significant impact on your report and perhaps that is the bigger challenge with your reporting.

• 0
  
  Joe Enos March 01, 2023 02:34

  CascadiaDataworks Thank you, I appreciate the insight.