CODEWITHSUNDEEP
sqlt-sqlstored-procedures
Cody Hicks
Cody Hicks

Reputation: 420

How can I COUNT rows from another table using a SELECT statement when joining?

this is the first time I've tried including a row count within a select statement. I've tried the following but including COUNT(other row) is apparently not allowed in the way I'm trying to do it. How can I include a row count from another table in a select statement, mainly consisting of objects from the first table?

-Thanks

...

SELECT
Reports.ReportID,
EmployeeADcontext,
ReportName,
CreatedDate,
COUNT(Expenses.ExpID) AS ExpCount,
ReportTotal,
Status

FROM
[dbo].[Reports]
INNER JOIN
[dbo].[Expenses]
ON 
[dbo].[Expenses].ReportID = [dbo].[Reports].ReportID

WHERE EmployeeADcontext = @rptEmployeeADcontext

Upvotes: 0

Views: 61

Answers (3)

user2858650
user2858650

Reputation:

You are missing your GROUP BY. Whenever you aggregate (SUM, COUNT, MAX, etc..) you always need to include a GROUP BY statement that includes all visible fields except your aggregated fields. So your code should read:

SELECT
Reports.ReportID,
EmployeeADcontext,
ReportName,
CreatedDate,
COUNT(Expenses.ExpID) AS ExpCount,
ReportTotal,
Status

FROM
[dbo].[Reports]
INNER JOIN
[dbo].[Expenses]
ON 
[dbo].[Expenses].ReportID = [dbo].[Reports].ReportID

WHERE EmployeeADcontext = @rptEmployeeADcontext

GROUP BY  Reports.ReportID, EmployeeADcontext, ReportName, CreatedDate, 
          ReportTotal,  Status

Here is some additional documentation on T-SQL GROUP BY.

Upvotes: 3

Donal
Donal

Reputation: 32813

You could use a sub-query to return the count. That way you don't need any joins. For example:

SELECT
r.ReportID,
r.EmployeeADcontext,
r.ReportName,
r.CreatedDate,
(select COUNT(e1.ExpID) FROM Expenses e1 where e1.ReportID = r.ReportId) AS ExpCount,
r.ReportTotal,
r.Status
FROM Reports r
WHERE r.EmployeeADcontext = @rptEmployeeADcontext

Upvotes: 2

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2374

You need a group by clause.

Add: 

GROUP BY
    Reports.ReportID,
    EmployeeADcontext,
    ReportName,
    CreatedDate,
    ReportTotal,
    Status

Upvotes: 2

Related Questions