I have the following SQL

```
DECLARE @StatusOK int
DECLARE @StatusFailed int

SELECT @StatusOK = COUNT(Status) FROM tblAuditServiceLog WHERE Status = '1'
SELECT @StatusFailed = COUNT(Status) FROM tblAuditServiceLog WHERE Status = '2'

SELECT CompanyId, MethodCalled, tblSystemCompany.Description, @StatusOK as StatusOK, @StatusFailed as StatusFail
FROM tblAuditServiceLog 
INNER JOIN tblSystemCompany ON tblAuditServiceLog.CompanyId = tblSystemCompany.SystemCompanyId
GROUP BY MyId, MethodCalled, tblSystemCompany.Description

```

This works but the count isn't based on the `CompanyId` (row), its returning the count across the table, so its a bit worthless.

From two tables like this

```
:: tblAuditServiceLog 
| CompanyId | MethodCalled | Status |
| 232       | GetProducts  |    1   |

:: tblSystemCompany
| SystemCompanyId | Description |
| 232             | MyCompany   |

```

Where Status = 1 is OK, 2 is Fail

Ideally I'd get an output like this...

```
| CompanyId | MethodCalled | Description | StatusOK | StatusFail |
| 232       | GetProducts  | MyCompany   |    8     |     2      |
| 236       | GetProducts  | MyCompanyB  |    8     |     2      |
| 256       | GetBrands    | MyCompanyC  |    8     |     2      |

```

I'm considering using cursors and just looping through and dumping into a temp table but I'm assuming there's a more optimal way of doing this.

Any help from some SQL Server geniuses appreciated

The results above are (as per the two Select @ queries) the results of counting all the Status = 1 (status ok) and all the status = 2 (status fail) and not, as it should be, Status = AND CompanyId = 232

So assuming multiple company's are logged doing various calls to the web-service, the count would be based on the total count of "OK Statuses" rather than the count for that company.