Think we need someone with OOo 3.3. As Sliderule pointed out (in some other post?) CASE WHEN works only since 3.3, before CASEWHEN or some other derivate was needed. But I'd rather use CASE WHEN and follow SQL standards.
In other words. You have attached the sample database file, that's good. But I can't test it, because my version is too old for this.
But I think the more relevant part here is the logic. You say
"ooBase is requiring the use of the GROUP BY command". No, that's wrong. It is always the decision of the user to use GROUP BY or not. Seems a bit that you came to the last state by trial-and-error ... occasionally adding a group by and suddenly the nasty error from before disappears.
Sometimes it is easier to understand what GROUP BY is doing if you start with a DISTINCT query:
SELECT
DISTINCT "Streamlining" FROM "FleetTest"
DISTINCT refers to all listed fields (here only one) and says if there is another "Streamlining" value 's' or 'p' that has already been seen in the records before, ignore it. For your sample table only 3 records. In short it eliminates duplicates. Now you may ask: How many duplicates do I have? This extra bit is given by GROUP BY:
SELECT
"Streamlining", count(*) AS "how_many" FROM "FleetTest"
GROUP BY "Streamlining"
Now you get:
Code: Select all
| Streamlining | how_many |
+--------------+==========+
| p | 1 |
| s | 2 |
| u | 1 |
The fields of a group-by select do always have fields that should be distinct (these fields must be repeated after the final GROUP BY) and all other fields have to be aggregation fields count(*), sum(...), avg(...)
A more elaborate example (you don't need to change the part after the group by if you add other aggregate fields):
SELECT "Streamlining", count(*) AS "how_many",
SUM("FuelCapacity") AS "Capacity" FROM "FleetTest" GROUP BY "Streamlining"
Code: Select all
| Streamlining | how_many | Capacity |
+--------------+==========+==========+ NOTE: Aggregation fields have double lines =====
| p | 1 | 100 |
| s | 2 | 21500 |
| u | 1 | 900 |
In this case you ask "How many ships of each streamlining category do I have? How much capacity do I have per category?" Which is easy because you have the category or characteristics directly as column in the table. Often you have a date column "the_day" in your table and want to see how much records or how many tons of goods you transport on Mondays. The answer is you have to massage your date column to only show the day of the week. Most database engines have functions for this: weekday("the_date") and you do:
SELECT count(*) AS how_many, sum(goods) AS total FROM the_table WHERE weekday("the_date") = 'Mon'
(Note: thats somehow pseudo code, some databases might require a numerical 1 or 2 depending on the locale.)
But you don't want to run a separate query for each weekday, so group-by will help you:
SELECT weekday("the_date") AS wday, count(*) AS how_many, sum(goods) AS total FROM the_table
GROUP BY weekday("the_date")
Code: Select all
| wday | how_many | total |
+------+==========+=======+
| Mon | 24 | 3400 |
| Tue | 0 | 0 |
| Wed | 45 | 6700 |
| Thu | 13 | 980 |
: : :
| Sun | 0 | 0 |
The crucial point here is that "weekday" filters the variety of 365 different days of a year into only 7 different output values. Your CASE-WHEN works in the same way: A variety of different input constellations are filtered to a set of 3 different output values/strings.
So you should really reconsider what you want to achieve. I am pretty sure you want to see the SUM or PERCENTAGE or something similar as an additional third column. But at the moment that's just swallowed.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.