求一个sql语句的写法？

[catcatcat12345]

有数据表 a

字段 name a b c d

001 1 2 3 4
001 5 6 7 4
001 2 3 4 8
002 1 2 3 4
002 3 2 2 5
003 2 2 2 4

问题：根据name 求出 a，b，c 三列的平均值？并且，再根据 name 求出 d 列里，小于等于4的个数比例和大于4个数的比例，每一个不同的name都要算。最好能放在一个表里！！
例如： name a平均 b平均 c平均 d小、等于deng4 d大于4
001 2.66 3.66 4.66 66% 33%
002 2 2 2.5 50% 50%
003 2 2 2 100% 0
最后，最好有个统计，就是所有的平均值和比例
比如： 总计 2.22 2.55 3.03 66% 33%

[bdmh]

select avg(xxx) from xxx group by name

[catcatcat12345]

求平均数我知道，我想知道怎么按name算比例，然后把平均数和比例放在一个表里

[jun471537173]

select name,
       avg(a),
       avg(b),
       avg(c),
       (select count(*)
          from a
         where name = t.name
           and d <= 4) / (select count(*)
                            from a
                           where name = t.name)
  from a t
 group by name

在plsql中测试通过，其实后面那些算比例的最好写成一个函数。。。

[flowfog]

select name, avg(a), avg(b), avg(c), (select count(*) from a where name = t.name and d <= 4) / (select count(*) from a where name = t.name) from a t group by name
这种方法算出的百分比不对

[天下如山]

貌似 SQL板块已经给你答案了吧
摘自楼主SQL版块帖子 4楼 magician547的答案

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'a')
BEGIN
    DROP TABLE A
END
GO
CREATE TABLE a
(
    name VARCHAR(10),
    a INT,
    b INT,
    c INT,
    d INT
)
GO
INSERT INTO a
SELECT  '001', 1, 2, 3, 4 UNION   
SELECT  '001', 5, 6, 7, 4 UNION
SELECT  '001', 2, 3, 4, 8 UNION
SELECT '002', 1, 2, 3, 4 UNION
SELECT  '002', 3, 2, 2, 5 UNION
SELECT  '003', 2, 2, 2, 4
GO



WITH t AS 
(SELECT name,SUM(a) / CAST(COUNT(1) AS  DECIMAL(10,2)) AS avga,SUM(b) / CAST(COUNT(1) AS DECIMAL(10,2)) AS avgb,SUM(c) / CAST(COUNT(1) AS DECIMAL(10,2)) AS avgc,SUM(CASE WHEN d <= 4 THEN 1 ELSE 0 END) AS small4,SUM(CASE WHEN d > 4 THEN 1 ELSE 0 END) AS big4
FROM a
GROUP BY name),
m AS
(
SELECT name,avga,avgb,avgc,RTRIM(small4 * 100  / (small4 + big4)) + '%' as small4,RTRIM(big4 * 100  / (small4 + big4)) + '%' as big4
FROM t
)

SELECT * FROM m
UNION
SELECT '总计',avg(t.avga),avg(T.avgb),avg(T.avgc),RTRIM(SUM(t.small4) * 100  / (SUM(t.small4) + SUM(t.big4))) + '%' as small4,RTRIM(SUM(t.big4) * 100  / (SUM(t.small4) + SUM(t.big4))) + '%' as big4
FROM t

name    avga    avgb    avgc    small4    big4
001    2.66666666666    3.66666666666    4.66666666666    66%    33%
002    2.00000000000    2.00000000000    2.50000000000    50%    50%
003    2.00000000000    2.00000000000    2.00000000000    100%    0%
总计    2.22222222222    2.55555555555    3.05555555555    66%    33%

[catcatcat12345]

已经有答案乐儿，谢谢各位的帮助！

[昵称不合法]

select [name],avg(a),avg(b),avg(c),
rtrim(convert(char,(convert(float,count(case when d<=4 then '1' end))/convert(float,count(*))*100.00)))+'%',
rtrim(convert(char,(convert(float,count(case when d>4 then '1' end))/convert(float,count(*))*100.00)))+'%'
from testa group by [name]

函数略多。。。估计还要加一个函数来截掉比例值小数点后的数。。。

[昵称不合法]

select [name],avg(a),avg(b),avg(c),
rtrim(convert(char,(convert(float,count(case when d<=4 then '1' end))/count(*)*100)))+'%',
rtrim(convert(char,(convert(float,count(case when d>4 then '1' end))/count(*)*100)))+'%'
from testa group by [name]

=-=。再改进试试。

[H_Gragon]

select name, avg(a) as 'a平均',avg(b) as 'b平均',avg(c) as 'c平均',str((select count(*) from a where d>4 )*100/(select count(*) from a ))+'%' as 'd大于4',str((select count(*) from a where d<=4 )*100/(select count(*) from a))+'%' as 'd小于等于4' from a group by name

[H_Gragon]

select name, avg(a) as 'a平均',avg(b) as 'b平均',avg(c) as 'c平均',str((select count(*) from a where d>4 )*100/(select count(*) from a ))+'%' as 'd大于4',str((select count(*) from a where d<=4 )*100/(select count(*) from a))+'%' as 'd小于等于4' from a group by name 

[昵称不合法]

select [name],avg(a),avg(b),avg(c),
rtrim(round(convert(float,count(case when d<=4 then '1' end))/count(*)*100,0))+'%',
rtrim(round(convert(float,count(case when d>4 then '1' end))/count(*)*100,0))+'%'
from testa group by [name]

各种简化，终于可以了。