CODEWITHSUNDEEP
sqlsql-servergroup-by
xarqt
xarqt

Reputation: 197

Count and group SQL Server table data

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 .

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?

Upvotes: 1

Views: 332

Answers (3)

R.Kabir
R.Kabir

Reputation: 11

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

Upvotes: 0

Liaqat Kundi
Liaqat Kundi

Reputation: 71

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 2

Related Questions