一个关于sql语句怎么实现有关问题_Oracle开发

一个关于sql语句如何实现问题
表如下：tableA
bureau_id field1
100 A
100 A
100 B
101 A
101 B
101 A
101 A
我希望查询结果为：
bureau_id count(*) fieldA
100 3 2
101 4 3

也就是以bureau_id分组查询出每组的总数 select count（*） from tableA group by bureau_id 这样得到已bureau_id分组的数据
bureau_id count(*)
100 3
101 4
同事也要得到以bureau_id分组的同值的field1的数据select count（*） from tableA group by bureau_id，field1 having field1=‘A’并和上面查询结果做对应。

不知道我的问题描述明白了没有？希望大家给各帮助。

------解决方案--------------------
try it ..

SQL code

SQL> select bureau_id,  2         count(1) as all_counts,  3         sum(decode(field1,'A',1,0)) as A_counts  4    from tableA tt  5   group by bureau_id; BUREAU_ID ALL_COUNTS   A_COUNTS---------- ---------- ----------       100          3          2       101          4          3
------解决方案--------------------
try it ..

SQL codeSQL>  select *  2     from tableA tt; BUREAU_ID FIELD1     FIELD2     FIELD3---------- ------ ---------- ----------       100 A               1          2       100 A               3          2       100 B               7          5       101 A               2          3       101 B               6          6       101 A              11          9       101 A              17          87 rows selectedSQL> SQL> select bureau_id,  2         count(1) as all_counts,  3         sum(decode(field1,'A',1,0)) as a_counts,  4         sum(decode(sign(field2-field3),1,1,0)) as field2_large_than_field3  5    from tableA tt  6   group by bureau_id; BUREAU_ID ALL_COUNTS   A_COUNTS FIELD2_LARGE_THAN_FIELD3---------- ---------- ---------- ------------------------       100          3          2                        2       101          4          3                        2SQL>