SQL Use grouped values to use in a subquery + "where column in())"
I have the folowing table:
Case with columns > ID,ResponsibleID
Action with columns > ID,CaseID,Action
I want to get a list of all responsibles with their number of cases and their number of total actions over all those cases.
This could be the query if it worked, but it does not
Select
Case.ResponsibleID,
CaseCount=count(*),
--how can i tell sql that this is the CaseID sublist of the group?
--It would be possible with a subquery, but i rather not do that for performance reasons.
--the real query gets the caseID list from a table 3 or 4 joins later.
ActionCount=(select count(*) from Action wh开发者_如何学JAVAere CaseID in Case.CaseID)
From Case
Group by ResponsibleID
SELECT c.ResponsibleID
, COUNT(DISTINCT c.ID)
, COUNT(*)
FROM Case c
LEFT JOIN Action a ON a.CaseID = c.CaseID
GROUP BY
c.ResponsibleID
精彩评论