none
Group By on only one field in a query? RRS feed

  • Question

  • Hi, (I suspect I'm approaching this all wrong)

    I have a 3500 record data table containing a list of scheduled flights for the coming month.

    A query selects Flight Numbers with particular characteristics, eg Flight Duration.

    The result contains all the Flight Numbers, often dozens of the same flight number as they depart daily throughout the month.

    A further query contains a Total function with Group By starting with Flight Number to get rid of the duplicates and just show a list of unique Flight Numbers.

    However as the Total function in a query seems to set a Group By parameter for every field I need to display I still get multiple copies of some flights as the Flight Duration field may vary by 5 minutes either way during the month, making them each unique.

    Is there a way of returning a list Grouped By only one field?

    Many thanks

    Monday, May 16, 2016 9:22 AM

Answers

  • Remove fields that you don't really need from the query.

    Set the Total option for fields on which you don't want to group to something other than Group By.

    Example: if you're not really interested in Flight Duration, omit it from the query. If you do need it, set its Total option to Min if you want to return the shortest duration, or Avg if you want the average duration, or First if you want to return the first duration the query encounters (which is rather random), etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, May 16, 2016 9:36 AM

All replies

  • Remove fields that you don't really need from the query.

    Set the Total option for fields on which you don't want to group to something other than Group By.

    Example: if you're not really interested in Flight Duration, omit it from the query. If you do need it, set its Total option to Min if you want to return the shortest duration, or Avg if you want the average duration, or First if you want to return the first duration the query encounters (which is rather random), etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, May 16, 2016 9:36 AM
  • Hi Dom Marsh,

    please check the suggestion given by the Hans Vogelaar ,

    it can solve your issue and let us know it worked for you or not.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, May 17, 2016 5:50 AM
    Moderator