Declare @MyTable as Table
([COMPUTERNAME] Varchar(10), COUNTER Integer, REASON varchar(12))


insert into @mytable Values ('WXP-1', 3,'Failed')
insert into @mytable Values ('WXP-11', 13,'Failed')
insert into @mytable Values ('WXP-3', 8,'Failed')
insert into @mytable Values ('WXP-9', 10,'Failed')
insert into @mytable Values ('WXP-11', 7 ,'Success')


SELECT T1.COMPUTERNAME
     , SUM(t1.COUNTER) AS COUNTERTOTAL
     , T1.REASON
     , (SELECT SUM(T2.COUNTER)FROM @MYTABLE T2 WHERE T2.COMPUTERNAME = T1.COMPUTERNAME) As Occurances
     , CAST(SUM(t1.COUNTER) AS FLOAT)/(SELECT SUM(T2.COUNTER)FROM @MYTABLE T2 WHERE T2.COMPUTERNAME = T1.COMPUTERNAME)as Percentage
FROM @mytable T1
GROUP BY T1.COMPUTERNAME , T1.REASON
ORDER BY T1.COMPUTERNAME, T1.REASON

quote:

---

Originally posted by Tomji

I have a table as below:
COMPUTERNAME, COUNTER, REASON
WXP-1 3 Failed
WXP-11 13 Failed
WXP-3 8 Failed
WXP-9 10 Failed
WXP-11 7 Success

What I would like to do is create a percentage of Success vs. the Failed for the same Computername.
Problem is when I use Group by I cannot tell the COUNTER of the Success anymore. This is what I have been using:

SELECT COMPUTERNAME, SUM(COUNTER) AS COUNTERTOTAL
FROM
ReturnTable
GROUP BY COMPUTERNAME

Thank you. The above query actually targets a result table from another query, but that should not matter.

---

SELECT COMPUTERNAME,
       SUM(CASE WHEN REASON='Falied' THEN COUNTER ELSE 0 END) AS Failure,
       SUM(CASE WHEN REASON='Success' THEN COUNTER ELSE 0 END) AS Success,
       ISNULL(SUM(CASE WHEN REASON='Success' THEN COUNTER ELSE 0 END)/NULLIF(SUM(COUNTER),0),0) AS SuccessPercentage
FROM Table
GROUP BY COMPUTERNAME

quote:

---

Originally posted by Tomji

Thanks for both, they help me improve my SQL a lot.

visakh16, your percentage did not work for me, always returned 0. I have no prior expirience with ISNULL and NULLIF, the error must be there somewhere.

I use the below now:
CAST(CAST(SUM(CASE WHEN Reason='Success' THEN Counter ELSE 0 END) AS Decimal(10,3)) / CAST(SUM(Counter) AS Decimal(10,3)) * 100 AS Decimal(10,3)) AS SuccessPercentage

---

quote:

---

Originally posted by Tomji

I guess I now yet again more... division by 0 :)

,ISNULL(CAST(CAST(SUM(CASE WHEN Reason='Success' THEN Counter ELSE 0 END) AS Decimal(10,3)) / NULLIF(CAST(SUM(CASE WHEN Reason IN ('Success', 'Failed') THEN Counter ELSE 0 END) AS Decimal(10,3)),0) * 100 AS Decimal(10,3)),0) AS SuccessPercentage

The reason why I list Success and Failed seperatly is because I now include two other status reasons that I do not want to calculate into the percentage.

---

Declare @MyTable as Table
([COMPUTERNAME] Varchar(10), COUNTER Integer, REASON varchar(12))


insert into @mytable Values ('WXP-1', 3,'Failed')
insert into @mytable Values ('WXP-11', 13,'Failed')
insert into @mytable Values ('WXP-3', 8,'Failed')
insert into @mytable Values ('WXP-9', 10,'Failed')
insert into @mytable Values ('WXP-11', 7 ,'Success')

SELECT *
FROM 
(
SELECT T1.COMPUTERNAME
     , T1.REASON
     , CAST(SUM(t1.COUNTER) AS FLOAT)/(SELECT SUM(T2.COUNTER)FROM @MYTABLE T2 WHERE T2.COMPUTERNAME = T1.COMPUTERNAME)as Percentage
FROM @mytable T1
GROUP BY T1.COMPUTERNAME , T1.REASON
) As Results
PIVOT 

(
   Max( percentage)
   FOR [REASON] IN ([Failed],[Success])
) As PvtResult

quote:

---

The reason for using ISNULL and NULLIF was to avoid the division by 0 error. Are you still getting it?

---

quote:

---

Originally posted by Tomji

quote:

---

The reason for using ISNULL and NULLIF was to avoid the division by 0 error. Are you still getting it?

---

Nope, it's all fine now.

Thanks again Dallr and visakh16. I have saved both queries for future use.

---