How do I add columns based on subquery counts in SQL Server 2005

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, tblSystemCompan开发者_如何学Pythony.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.

---

You just need a conditional COUNT.

SELECT
     MyId, MethodCalled, Description,
     COUNT(CASE Status WHEN 1 THEN tblAuditServiceLog.MyId END) AS StatusOK, 
     COUNT(CASE Status WHEN 2 THEN tblAuditServiceLog.MyId END) AS StatusFail 
FROM 
    ...

• There is an implied ELSE NULL in this CASE
• COUNT ignores NULL
• You may need DISTINCT in the COUNT so (COUNT(DISTINCT CASE...) but probably not

Observations:

• Your WHERE is not needed. The "table match condition" is already in the JOIN
• Use aliases and qualify columns

So (assumptions made about MethodCalled and Description columns)

SELECT  ASL.CompanyId, 
        ASL.MethodCalled, 
        SI.Description, 
        COUNT(CASE ASL.Status WHEN 1 THEN ASL.CompanyId END) AS StatusOK, 
        COUNT(CASE ASL.Status WHEN 2 THEN ASL.CompanyId END) AS StatusFail

FROM    dbo.tblAuditServiceLog AS ASL INNER JOIN
        dbo.tblSystemCompany AS 
        SI ON ASL.CompanyId = SI.SystemCompanyId

GROUP BY ASL.CompanyId, ASL.MethodCalled, SI.Description

---

SELECT 
    MyId, 
    MethodCalled, 
    Description, 
    StatusOK   = SUM(CASE WHEN Status = '1' THEN 1 ELSE 0 END),
    StatusFail = SUM(CASE WHEN Status = '2' THEN 1 ELSE 0 END)
FROM tblAuditServiceLog ...