How to use Splunk to create a table using multiple fields?

[suvi6789]

Hi,
I want to create a splunk table using multiple fields. Let me explain the scenario
I have the following fields

Name
Role (multiple roles will exist for each name)
HTTPrequest (There are multiple response as 2**,3**,4** and 5**)

My final output  should be when the query is ran, It should the group the data in the below format for every day

Date	Name	Role	Success	Failed	Total	Failed %
01-Jan-23	Rambo	Team lead	100	0	100	0
01-Jan-23	Rambo	Manager	100	10	110	10
01-Jan-23	King	operator	2000	100	2100	5
02-Jan-23	King	Manager	100	0	100	0
03-Jan-23	cheesy	Manager	100	10	110	10
04-Jan-23	cheesy	Team lead	4000	600	4600	15

So, What I tried is 
index=ABCD
| bucket _time span=1d
| eval status=case(HTTPrequest < 400,"Success",HTTPrequest > 399,"Failed" )
| stats count by _time Name Role status

This works something as below but I need the success and failure  in to 2 seperate columns as I have shown above and also I need to add the failed % and total

Date	Name	Role	HTTPStatus	COUNT
01-Jan-23	Rambo	Team lead	Success	100
01-Jan-23	Rambo	Team lead	Failed	0
01-Jan-23	Rambo	Manager	Success	100
01-Jan-23	Rambo	Manager	Failed	10
01-Jan-23	King	operator	Success	2000
01-Jan-23	King	operator	Failed	200
02-Jan-23	King	Manager	Success	10
03-Jan-23	cheesy	Manager	Success	300
04-Jan-23	cheesy	Team lead	Success	400

I used the chart count over X by Y but this allows me to use only 2 fields and not more than 2

Please could you suggest me on how to get this sorted. 

[etoombs]

In your stats statement, add the other fields you need using evals: count(eval(status="Success")) as Success, count(eval(status="Failed")) as Failed, and remove the status from the by clause. After the stats, do an eval to calculate your percentages. 

[suvi6789 · Accepted solution]

Hi etoombs,

Many thanks for the suggestion, I got that sorted.ta

[suvi6789]

It worked perfectly for me. Thank you again.