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?
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).
desired_group ------------- 0 1 2 3 4 5 6
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;
Answered By – mgojohn
Answer Checked By – Jay B. (AngularFixing Admin)