Ms-access – Access Web App query 3 Tables with aggregatios – Need a workaround

Your query is missing AND C.Accommodation_ID = B.Accommodation_ID

If you are just getting started with SQL then you might consider building simple queries like this in the Access query designer and then switch to SQL view to see what the query designer has generated. In your case we have

and Access generates the following SQL (which I've indented for clarity)

SELECT 
    Instance.Description, 
    City.CityName, 
    Accommodation.CategoryType
FROM 
    (
        City 
        INNER JOIN 
        Instance 
            ON City.City_ID = Instance.City_ID
    ) 
    INNER JOIN 
    Accommodation 
        ON Instance.Accommodation_ID = Accommodation.Accommodation_ID
WHERE (((City.CityName)="Melbourne") AND ((Accommodation.CategoryType)="Hotel"));

Note that the query uses INNER JOIN syntax, which is generally preferred over the older method of putting the join conditions in the WHERE clause.

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

or

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end