Ryan Hargreaves Ryan Hargreaves - 5 months ago 6
SQL Question

Returning results of a query against results of same query with a join

I'm struggling to put this into words, hence the bad title;

I want to return the results of one query alongside the results of the same query inner joined to another table (so, total number of value1 against number of value1 that appears in the other table). So I want to return the count per owner of the following query;

SELECT DISTINCT(Owner), COUNT(Owner) FROM Actions
INNER JOIN [DataCapture].[dbo].DataCapture ON Actions.Contact_ID = DataCapture.ID
GROUP BY Owner ORDER BY Owner


And then I want to find out the percentage from the total;

SELECT DISTINCT(Owner), COUNT(Owner) FROM Actions
GROUP BY Owner ORDER BY Owner


I thought INTERSECT or EXCEPT would be the way, but since I am returning distinct counts it doesn't work very well.

EDIT: For clarity, the results of the first and second queries are as follows respectively;

USER1 212
USER2 613
USER3 155
USER4 375
USER5 8
USER6 76



USER1 1218
USER2 849
USER3 237
USER4 1062
USER5 39
USER6 418


So what I want is, in 1 query, the results of the first query over the results of the second query to give me the percentage.

Answer

You could do this with a CTE (I guess you're working with Sql Server) or an inner query

select a.Owner, count(*), totalActions, (count(*) * 1.0 / totalActions) * 100 as yourpercentage
from Actions a
join DataCapture db on db.Id = a.Contact_Id
join (select Owner, count(*) as totalActions from Actions
      group by Owner) total on total.Owner = a.Owner
group by a.Owner, total.totalActions

No need of distinct with group by, by the way