CODEWITHSUNDEEP
sqlitegroup-byrange
aplavin
aplavin

Reputation: 2229

Group by ranges in SQLite

I have a SQLite table which contains a numeric field field_name. I need to group by ranges of this column, something like this: SELECT CAST(field_name/100 AS INT), COUNT(*) FROM table GROUP BY CAST(field_name/100 AS INT), but including ranges which have no value (COUNT for them should be 0). And I can't get how to perform such a query?

Upvotes: 0

Views: 1985

Answers (1)

mgojohn
mgojohn

Reputation: 920

You can do this by using a join and (though kludgy) an extra table.

The extra table would contain each of the values you want a row for in the response to your query (this would not only fill in missing CAST(field_name/100 AS INT) values between your returned values, but also let you expand it such that if your current groups were 5, 6, 7 you could include 0 through 10.

In other flavors of SQL you'd be able to right join or full outer join, and you'd be on your way. Alas, SQLite doesn't offer these.

Accordingly, we'll use a cross join (join everything to everything) and then filter. If you've got a relatively small database or a small number of groups, you're in good shape. If you have large numbers of both, this will be a very intensive way to go about this (the cross join result will have #ofRowsOfData * #ofGroups rows, so watch out).

Example:

TABLE: groups_for_report

desired_group
-------------
0
1
2
3
4
5
6

Table: data

fieldname    other_field
---------    -----------
250          somestuff
230          someotherstuff
600          stuff

you would use a query like

select groups_for_report.desired_group, count(data.fieldname)
from data
cross join groups_for_report
where CAST(fieldname/100.0 AS INT)=desired_group
group by desired_group;

Upvotes: 1

Related Questions