A表
id brand
1 飘柔 2 piao rong
3 海飞丝
4 飘柔 得到的结果为
品牌 数量 所有总数 占比
飘柔 2 4 50%
piaorong 1 4 25%
海飞丝 1 4 25%
id brand
1 飘柔 2 piao rong
3 海飞丝
4 飘柔 得到的结果为
品牌 数量 所有总数 占比
飘柔 2 4 50%
piaorong 1 4 25%
海飞丝 1 4 25%
id brand
1 飘柔 2 piao rong
3 海飞丝
4 飘柔 得到的结果为
品牌 数量 所有总数 占比
飘柔 2 4 50%
piaorong 1 4 25%
海飞丝 1 4 25%
group by 品牌,所有总数
brand as 品牌,
count(1) as 数量,
(select count(1) from a) as 所有总数,
ltrim(count(1)*100.0/(select count(1) from a))+'%'
from
a
group by
brand
go
create table [A]([id] int,[brand] varchar(10))
insert [A]
select 1,'飘柔' union all
select 2,'piao rong' union all
select 3,'海飞丝' union all
select 4,'飘柔'
select brand as 品牌,count(1) as 数量,b.cnt as 所有总数,
ltrim(count(1)*100/b.cnt)+'%'
from a
join(select count(1) as cnt from a) b on 1=1
group by a.brand,b.cnt
/**
品牌 数量 所有总数
---------- ----------- ----------- -------------
piao rong 1 4 25%
海飞丝 1 4 25%
飘柔 2 4 50%(3 行受影响)
**/
深谷幽兰 5 144017 0按照你这样来的话 怎么占比是0呢?
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[brand] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','飘柔' UNION ALL
SELECT '2','piaorong' UNION ALL
SELECT '3','海飞丝' UNION ALL
SELECT '4','飘柔'--SELECT * FROM [tb]-->SQL查询如下:
SELECT brand 品牌, COUNT(1) AS 数量, (
SELECT COUNT(1)
FROM tb
) AS 总数, LTRIM(
CAST(
COUNT(1)*100./(
SELECT COUNT(1)
FROM tb
) AS INT
)
)+'%' AS 百分比
FROM tb
GROUP BY brand
ORDER BY 2 desc/*
品牌 数量 总数 百分比
---------- ----------- ----------- -------------
飘柔 2 4 50%
piaorong 1 4 25%
海飞丝 1 4 25%(3 行受影响)
*/
那就要带上小数了,如下select 品牌,count(1) as 数量,所有总数,占比=ltrim(count(1)*100.0/所有总数)+'%' from A表,(select count(1) 所有总数 from A表) t
group by 品牌,所有总数
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[brand] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','飘柔' UNION ALL
SELECT '2','piaorong' UNION ALL
SELECT '3','海飞丝' UNION ALL
SELECT '4','飘柔'--SELECT * FROM [tb]-->SQL查询如下:
SELECT brand 品牌, COUNT(1) AS 数量, (
SELECT COUNT(1)
FROM tb
) AS 总数, LTRIM(
CAST(
COUNT(1)*100./(
SELECT COUNT(1)
FROM tb
) AS FLOAT
)
)+'%' AS 百分比
FROM tb
GROUP BY brand
ORDER BY 2 DESC
/*
品牌 数量 总数 百分比
---------- ----------- ----------- ------------------------
飘柔 2 4 50%
piaorong 1 4 25%
海飞丝 1 4 25%(3 行受影响)
*/
insert into tb select 1,'飘柔' union all
select 2,'pao rong' union all
select 3,'海飞丝' union all
select 4,'飘柔'
go
select brand as 品牌,count(*)as 数量,(select count(*) from tb) as 所有数量,convert(varchar,convert(int,100.0*count(*)/(select count(*) from tb)))+'%' as 占比 from tb group by brand
go
drop table tb
/*
品牌 数量 所有数量 占比
---------- ----------- ----------- -------------------------------
pao rong 1 4 25%
海飞丝 1 4 25%
飘柔 2 4 50%(3 行受影响)*/