CODEWITHSUNDEEP
mysql
user299791
user299791

Reputation: 2070

How to join two select on same table

I have two select statement like

Select author_id, count(text) from posts group by author_id

select author_id, count(text) from posts where postcounter =1 group by author_id

Is there a way to combine in a single query the two statements? Results differ in length, so it is needed to insert some 0s in the second resultset.

Many thanks for any help

Best regards, Simone

Upvotes: 1

Views: 72

Answers (3)

Tucker
Tucker

Reputation: 7362

You can try a union all statement?

   SELECT `id`,sum(`count`) FROM (
Select  author_id as `id`, count(text) as `count` from posts group by author_id 
UNION ALL 
select author_id as `id`, count(text) as `count` from posts where postcounter =1 group by author_id
    )

Upvotes: 0

Taryn
Taryn

Reputation: 247810

You should be able to get this in a single query using:

Select author_id, 
  count(text) TextCount,
  count(case when postcounter=1 then text end) PostCount
from posts 
group by author_id

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

Is this what you're looking for?

select author_id, 
   sum(case when postcounter = 1 then 1 else 0 end) count1, 
   sum(case when postcounter <> 1 then 1 else 0 end) count2,
   count(text) allcount
from posts  
group by author_id

Upvotes: 1

Related Questions