Request joining the results of two other requests with GROUP BY clause in SQL Server 2005

Question

I often find myself doing something along the following lines in sql server 2005 :

Step1:

create view view1 as 
select count(*) as delivery_count, clientid from deliveries group by clientid;

Step2:

create view view2 as 
select count(*) as action_count, clientid from routeactions group by clientid;

Step3 :

select * from view1 inner join view2 on view1.clientid = view2.clientid

Is it possible to obtain the same final result in only one statement, avoiding the creation of the views ?

Answer 1

I can't think of any way off the top of my head, unless there's some sort of relationship between routeactions and deliveries that you haven't mentioned. Without that (an FK from one to the other, most likely), you can't do a join without distorting the numbers on one or both tables.

Answer 2

SELECT
    clientid,
    (SELECT COUNT(*) FROM deliveries where clientid = clientIds.clientid) AS 'delivery_count',
    (SELECT COUNT(*) FROM routeactions WHERE clientid = clientIds.clientid)
AS 'action_count'
FROM
(
SELECT clientid FROM deliveries
UNION
SELECT clientid FROM routeactions
) clientIds

I believe that should work, there are easier solutions if you have a clients table

Answer 3

Sure, use nested queries:

select *
from (select count(*) as delivery_count, clientid 
      from deliveries group by clientid) AS view1
inner join (select count(*) as action_count, clientid
            from routeactions group by clientid) AS view2
    on view1.clientid = view2.clientid

Or with the new CTE syntax you can have:

WITH view1 AS (
    select count(*) as delivery_count, clientid from deliveries group by clientid
), view2 AS (
    select count(*) as action_count, clientid from routeactions group by clientid
)
select * from view1 inner join view2 on view1.clientid = view2.clientid