soda-js Group By returns "Invalid SoQL query"

Question

I'm using soda-js to query the USAC E-rate databases and I need to add Group By to my query. Soda-js has a group method as part of the query function, but whenever I include it, the http request fails.

Here is the code that returns a response:

consumer.query()
  .withDataset(dataset_id)
  .limit(50)
  .select('*')
  .where({ org_state: 'CO' })
  .getRows()
    .on('success', function(rows) { console.log(rows); })
    .on('error', function(error) { console.error(error); });

Here is the code with .group that returns a 400 (Bad Request) error:

consumer.query()
  .withDataset(dataset_id)
  .limit(50)
  .select('*')
  .where({ org_state: 'CO' })
  .group('billed_entity_number') // <------ ADDED ROW
  .getRows()
    .on('success', function(rows) { console.log(rows); })
    .on('error', function(error) { console.error(error); });

If I change .group('billed_entity_number') to .group(null) the query works.

I can't seem to find why this is failing, also given the SoQL docs on Group.

Any Help?

Answer 1

After reviewing the documentation further, I found the poorly-worded sentence:

$group must be used in conjunction with $select to provide the aggregation functions you wish to use.

I don't consider myself competent in SQL, but last time I checked, aggregate functions aren't required to use GROUP BY. So I guess SoQL is unique in that sense.

---

For my case, I put MAX() around all of my SELECT fields and it returned as I wanted it to:

consumer.query()
  .withDataset(dataset_id)
  .limit(50)
  .select('billed_entity_number','MAX(org_state) as org_state','MAX(billed_entity_name) as billed_entity_name')
  .where({ org_state: 'CO' })
  .group('billed_entity_number')
  .getRows()
    .on('success', function(rows) { console.log(rows); })
    .on('error', function(error) { console.error(error); });