CODEWITHSUNDEEP
sqlsql-server
Doe
Doe

Reputation: 379

Combine multiple queries into one query

I am trying to group by clientid and m.id. How can I convert these 2 queries into 1 query?

Query 1

select cs.clientid, count(distinct(m.id)) as cp
into #temp
from call_table cs 
join mem_table m 
on cs.CLIENTID = m.clientid
join activity_table a 
on a.memid = m.id 
where a.activity = 'abc'
group by cs.clientid, m.id

Query 2 

select clientid, sum(cp) from #temp
group by ClientId

Update

More specifically, I am looking to write this query without using a temp table.

Upvotes: 0

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Why wouldn't you just write this as:

with temp as (
      select cs.clientid, count(distinct m.id) as cp
      from call_table cs join
           mem_table m 
           on cs.CLIENTID = m.clientid join
           activity_table a 
           on a.memid = m.id 
      where a.activity = 'abc'
      group by cs.clientid, m.id 
     )
select clientid, sum(cp)
from temp
group by ClientId;

I can't figure out what the logic should really be, but this seems like the simplest way to combine the queries.

I would speculate that you simply want this query:

      select cs.clientid, count(distinct m.id) as cp
      from call_table cs join
           mem_table m 
           on cs.CLIENTID = m.clientid join
           activity_table a 
           on a.memid = m.id 
      where a.activity = 'abc'
      group by cs.clientid;

That is, remove m.id from the group by. And this can in turn be simplified to:

      select m.clientid, count(distinct m.id) as cp
      from mem_table m join
           activity_table a 
           on a.memid = m.id 
      where a.activity = 'abc'
      group by cs.clientid;

Assuming that m.id is unique and non-NULL, this can be further simplified to:

      select m.clientid, count(*) as cp
      from mem_table m join
           activity_table a 
           on a.memid = m.id 
      where a.activity = 'abc'
      group by cs.clientid;

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

You could group only by clientid

And since no fields from activity_table are selected, an EXISTS can be used instead.

select cs.clientid, count(distinct m.id) as cp
from call_table cs 
join mem_table m on m.clientid = cs.clientid
where exists 
(
    select 1
    from activity_table a 
    where a.memid = m.id 
      and a.activity = 'abc'
)
group by cs.clientid

Upvotes: 1

Related Questions