When using the report builder and selecting a number of fields, where I group by just one of the fields, the builder will return 0 results unless I group by all the fields in the select clause.  I'm not familiar with having to need to do this in any SQL

Version 1

    Details

    When using the report builder and selecting a number of fields, where I group by just one of the fields, the builder will return 0 results unless I group by all the fields in the select clause.

    I'm not familiar with having to need to do this in any SQL queries and I'm grouping by the request id, and the other selected fields relate to the request directly, on a 1:1 basis, so I don't understand why I need to group by them all to get a result.

    Why does it do this?


    Resolution

    In standard SQL rules, when you use GROUP BY, you must list all the result columns, that are not aggregated, in the GROUP BY clause. 

    MySQL is one SQL language that, as an exception to the rule, does allow you to just GROUP BY one of the fields without needing to list them all, when used directly. This may be why this requirement is unfamiliar to some.

    There are various pages that document this requirement online. Here is one example:

    http://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql