Access: query with grouping criteria

3285 views sql
4

I would like to group data of Table1 with a query that should give me Table2 as output.

Table2.FieldD is the count of Table1.FieldB where the grouping criteria is fulfilled.

The criteria is this: Group1 is made by all the record of Table1 where Table1.FieldA starts with "AK" or "KN". Group2 is the complementary of Group1.

enter image description here

answered question

1 Answer

12

Here's one option using case:

select fieldc,
    sum(fieldb) as fieldd
from (
    select fieldb, 
       case when fielda like 'ak%' or fielda like 'kn%' then 'group1' else 'group2' end as fieldc
    from table1
    ) t
group by fieldc
order by fieldc

posted this

Have an answer?

JD

Please login first before posting an answer.

Ads

Categories