对sql server表数据进行计数和分组

sqserrrh 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(326)

我有一张table attempts 像这样有几百张唱片。它记录了一些交易。事务的名称很重要，错误表明事务未成功。

| id | name  |error| data |
|----|-------|-----|------|
|   1| sara  | 0   |  bla |   
|   2| sara  | 1   |  bla |
|   3| sara  | 0   |  bla |
|   4| john  | 1   |  bla |
|   5| paul  | 0   |  bla |
|   6| paul  | 0   |  bla |
|   7| john  | 0   |  bla |

我要做的是通过按“name”分组来获得错误率和成功率。例如，对于名称“sara”，我有三个事务，两个是成功的（因为error是0（false）），一个是不成功的（因为error是1（true））。我想得到成功率或错误率的百分比。
成功率是三分之二
错误率是三分之一
我有点困惑，因为我不知道该怎么做：

SELECT  COUNT(id) AS 'count', name, error 
FROM attempts
GROUP BY name, error
ORDER BY  name

查询返回如下结果集：

|count| name  |error|
|----|-------|-----|
|   2| sara  | 0   |   
|   1| sara  | 1   |  
|   1| john  | 1   | 
|   1| john  | 0   |  
|   2| paul  | 0   |

但我不知道怎么计算利率，我想要这样的东西

|count| name |error|success| 
|----|-------|-----|-------|
|   3| sara  | 1   |   2   |
|   2| john  | 1   |   1   | 
|   1| paul  | 0   |   1   |

有人能帮我吗？

sql sql-server group-by

来源：https://stackoverflow.com/questions/62452343/count-and-group-sql-server-table-data

3条答案

按热度按时间

b4lqfgs41#

可以使用聚合：

select name, count(*), sum(error) as error, sum(1 - error) as success
from attempts
group by name;

如果想要获得成功率，可以使用：

avg( 1.0 - error ) as success_rate

赞(0）回复(0）举报 2021-07-26

ykejflvf2#

试试下面这个。 select name, cont(*), sum(case when error=1 then 1 else 0 end) as error, sum(case when error=0 then 1 else 0 end) as success from attempts group by name;

赞(0）回复(0）举报 2021-07-26

fslejnso3#

请运行以下代码。你会得到你想要的结果。第一部分用于将数据插入#tmp表。

-----------insert data into #tmp table
select *
into #tmp
from (
    Select 1 as id, 'sara' as name, 0 as error, 'bla' as data
    union all
    Select 2 as id, 'sara' as name, 1 as error, 'bla' as data
    union all
    Select 3 as id, 'sara' as name, 0 as error, 'bla' as data
    union all
    Select 4 as id, 'john' as name, 1 as error, 'bla' as data
    union all
    Select 5 as id, 'paul' as name, 0 as error, 'bla' as data
    union all
    Select 6 as id, 'paul' as name, 0 as error, 'bla' as data
    union all
    Select 7 as id, 'john' as name, 0 as error, 'bla' as data
)r
select * from #tmp
--------------Main ans starts here-------------
select r.cnt as Count, r.Name as Name, r.er as Error, r.cnt-r.er as Success from (
    select count(*) as cnt, name, sum(error)er
    from #tmp
    group by name
) r
order by count desc
--------------end here-------------

赞(0）回复(0）举报 2021-07-26

我来回答

对sql server表数据进行计数和分组

3条答案

相关问题

热门标签

最新问答