CODEWITHSUNDEEP
countuniquesoqlsocrata
dfriestedt
dfriestedt

Reputation: 493

SoQL count query with Socrata API

I'm trying to count the unique number of case_numbers between 2014-02-27 and 2014-02-28 from the chicago crime database. The data source is here: http://data.cityofchicago.org/resource/ijzp-q8t2.json

The API docs are here: http://dev.socrata.com/docs/queries.html

I've come up with the query below, but it counts each unique case_number, individually - resulting in a count of 1 for each unique case. I want to count the total number of unique case_numbers that were updated_on between 2014-02-27 and 2014-02-28. It should return 680 records.

http://data.cityofchicago.org/resource/ijzp-q8t2.json?$select=case_number,count%28case_number%29&$group=case_number&$where=updated_on%20%3E%3D%20%272014-02-27T00%3A00%3A00%27%20AND%20updated_on%20%3C%20%272014-02-28T00%3A00%3A00%27

Thoughts on how to change this query to yield the desired results?

Upvotes: 0

Views: 695

Answers (1)

chrismetcalf
chrismetcalf

Reputation: 1566

Unfortunately SODA/SoQL aren't powerful enough yet to allow you to do the equivalent of a distinct count like you'd do in real SQL.

However, based on what I've been told about that Chicago Crimes dataset, Case Numbers are only ever duplicated in the case of multiple murder, so you should be able to just do a simple count query like this example.

I'd confirm that in your data, but depending on what you're looking for that might be OK.

Upvotes: 3

Related Questions