Count and group SQL Server table data

Question

I have a table attempts like this with hundreds or records. It records some transactions. The name of transaction is important, and the error tells that transaction was not successful.

| 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 |

What I want to do it to get the error and success rate by grouping them by 'name'. So for example, for name 'Sara' I have three transactions two are successful (because error is 0 (false)) and 1 unsuccessful (because error is 1 (true)). I want to get in percentage the success or error rate .

The success rate is 2 out of 3
The error rate is 1 out of 3

I a bit confused cause I don't know exactly how to do it:

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

The query returns a result set like this:

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

but I don't know how can I calculate the rate , I want to have something like this

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

Can someone help me please?

I removed the inconsistent database tags. Please tag only with the database you are really using. — Gordon Linoff
– Gordon Linoff, Commented Jun 18, 2020 at 14:31

Gordon Linoff · Accepted Answer · 2020-06-18 14:32:06Z

2

You can use aggregation:

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

If you want the success rate, you can use:

avg( 1.0 - error ) as success_rate

edited Jun 18, 2020 at 14:32

answered Jun 18, 2020 at 14:30

Gordon Linoff

1.3m63 gold badges713 silver badges860 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Liaqat Kundi · Accepted Answer · 2020-06-18 14:34:30Z

0

Try the below one.

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;

answered Jun 18, 2020 at 14:34

Liaqat Kundi

716 bronze badges

Comments

R.Kabir · Accepted Answer · 2020-06-18 15:08:44Z

Please run the below code. You will get your desired result. The first part is for inserting data into the #tmp table.

-----------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-------------

Collectives™ on Stack Overflow

Count and group SQL Server table data

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related