Query totals: Min (1 Viewer)

[mkaeser]

Hello All,

I am redesigning a database and there are some queries that use the Min total. I understand why you would use this for numeric fields, but I can't find anything online that explains why you would use Min for text fields. Is there any reason why you would or need to?

When I remove the MIN from text fields and run the query, I get a Project Coordination Center error that reads "You tried to execute a query that does no include the specified expression [FieldName] as part of an aggregate function." I take this to mean that if you use Totals for your query, every field needs a total type? Because THAT doesn't seem right...

 

[pbaldy]

I suppose you might use Min on a text field to return a blank value if there was one. Or they wanted the earliest alphabetically.

In a totals query, every field in the SELECT clause either needs to have an aggregate function applied to it or be included in the GROUP BY clause.

 

[mkaeser]

That makes sense, but all the fields are required so there should be no blanks. Is there a reason why you would choose Min over Group By?

 

[plog]

should be

That is my favorite expression when it comes to databases. People's expectations about their data rarely align with the reality of their data. Then when you present them with a conflict, they just sit there and explain how it shouldn't be.

Not ragging on you, per se. Ragging on every data job I've ever had and trying to get you to understand that you should never trust assumptions about data.

That is also why I sometimes use Min instead of Group By in some aggregate queries--to work around dirty data. Often the end user knows how many records should be returned, but because of dirty data using Group By returns more than they expect/want. To get around it and because the offending field isn't that important to the end result, I use Min on that field to force it to how many records they expect.

I suggest you do that to. Use Group By initially, then change it to Min and see if you get more records. If so, your assumptions are incorrect. Be sure to let the end user know and be prepared to get an earful of "shouldn't be's" but no real hlep in solving the issue.

 

[mkaeser]

That is my favorite expression when it comes to databases. People's expectations about their data rarely align with the reality of their data. Then when you present them with a conflict, they just sit there and explain how it shouldn't be.

Not ragging on you, per se. Ragging on every data job I've ever had and trying to get you to understand that you should never trust assumptions about data.

That is also why I sometimes use Min instead of Group By in some aggregate queries--to work around dirty data. Often the end user knows how many records should be returned, but because of dirty data using Group By returns more than they expect/want. To get around it and because the offending field isn't that important to the end result, I use Min on that field to force it to how many records they expect.

I suggest you do that to. Use Group By initially, then change it to Min and see if you get more records. If so, your assumptions are incorrect. Be sure to let the end user know and be prepared to get an earful of "shouldn't be's" but no real hlep in solving the issue.

Fantastic explanation, thank you so much!