乘以计数值

Question

我有一些笼子大小和笼子成本的数据，我需要这些数据来获得所有笼子大小的计数，并将这些计数乘以笼子成本：

cagesize    cagecost
M           1.23
L           2.3
M           1.23

我试过了：

SELECT cagesize, COUNT(*), (COUNT(*) * cagecost) AS 'total'
FROM cage;
GROUP BY cagesize;

但是它一直从GROUP BY行中出现错误

我希望结果是：

cagesize    COUNT(*)    Total
M           2           2.46
L           1           2.3

Answer 1

使用sum而不是乘法

SELECT cagesize, COUNT(*), sum(cagecost) AS 'total'
FROM cage
GROUP BY cagesize;

Answer 2

试试这个：

declare @t table (cagesize varchar(50),cagecost float)

insert into @t values ('M',1.23)
insert into @t values ('L',2.3)
insert into @t values ('M',1.23)

select cagesize,count(*) as cnt,sum(cagecost) as total from @t
group by cagesize

Answer 3

你可以试试这个。

  select cagesize, count(1) as 'Count', cagecost * count(1) as 'total' 
  from cage group by cagesize, cagecost