一个ACCESS2000的交叉查询问题..如何写SQL语句?

[一川烟草123]

表结构如下.
Group,type,price
a m 70
b n 50
c n 35
d m 20
e m 60
f n 80
c n 80
c m 80
结果如下:
Group type=M的COUNT,Type=M的sum,type=n的COUNT,Type=n的sum,所有TypeM的sum,所有Type的COUNT

数据库用的是access2000

[ferrytang]

SELECT tb.Type, Count(tb.Type) AS Type之Count, Sum(tb.Price) AS Price之Sum
FROM tb
GROUP BY tb.Type;
union
SELECT 'zzzz',Count(tb.Type) AS Type之Count, Sum(tb.Price) AS Price之Sum
FROM tb

[一川烟草123]

楼上的答案好像不符合我要求的输出格式
我自己的笨办法如下:
表格式
送检科室,类别,应收金额

类别只有('门诊'和'住院')两种
语句如下:

SELECT tzs.科别, tzs.人次 AS 总人次, (IIF(ISNULL(tzs.收费),0,tzs.收费)) AS 总收费, (IIF(ISNULL(tmz.人次),0,tmz.人次)) AS 门诊人次, tmz.收费 AS 门诊收费, (总人次-iif(isnull(门诊人次),0,门诊人次)) AS 住院人次, (总收费-iif(isnull(门诊收费),0,门诊收费)) AS 住院收费
FROM [select max(送检科室) as 科别 ,sum(应收金额) as 收费,count(*) as 人次 from data1 group by 送检科室]. AS tzs LEFT JOIN [select max(送检科室) as 科别 ,sum(应收金额) as 收费,count(*) as 人次 from data1 where 类别='门诊' group by 送检科室]. AS tmz ON tzs.科别=tmz.科别
不知道有没有更精简更高效的?

[一川烟草123]

VB版的数据库高手呢?

[leus]

SELECT Count(tb.Group) AS MyCount, Sum(tb.Price) AS MySum
FROM tb
where type=m
union
SELECT Count(tb.Group) AS MyCount, Sum(tb.Price) AS MySum
FROM tb
where type=n
union
SELECT Count(tb.Group) AS MyCount, Sum(tb.Price) AS MySum
FROM tb

[leus]

试试看是不是你想要的。
不过只是你的type只有两种才这么做。

[leus]

SELECT Count(*) AS MyCount, Sum(tb.Price) AS MySum
FROM tb
where type=m
union
SELECT Count(*) AS MyCount, Sum(tb.Price) AS MySum
FROM tb
where type=n
union
SELECT Count(*) AS MyCount, Sum(tb.Price) AS MySum
FROM tb