CODEWITHSUNDEEP
mysqlsqlcountpivot
mohan
mohan

Reputation: 15

sql query implement two count in one

SELECT (select count(u.ag_code) 
from table1 as u inner join table2 as tc 
on u.industry_id=tc.tempcatid 
where u.ag_code!=0) as agnt,
    (select count(u.ag_code) 
     from table1 as u inner join table2 as tc 
     on u.industry_id=tc.tempcatid where u.ag_code=0),as dircus,
tc.catename from table1 as u inner join table2 as tc 
where u.industry_id=tc.tempcatid 
group by tc.tempcatid

this query have error i need two count and category name in one query
this is the condition for count

  1. ag_code!=0
  2. ag_code=0

in table1 have column ag_code (this have 0 and nonzero value)

my result need like this

Full Texts
agent   customer    catename
11  3   Real Estate
15  1   Automobile
3   0   Medical
34  77  Business
1   45  Travel & Hotels
11  3   Construction & Engineering

Upvotes: 1

Views: 469

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

SELECT tc.catename,
       count(case when u.ag_code!=0 then 1 end) agnt,
       count(case when u.ag_code =0 then 1 end) dircus
from table1 as u
inner join table2 as tc on u.industry_id=tc.tempcatid 
group by tc.tempcatid, tc.catename

Upvotes: 1

Giovanni Bekker
Giovanni Bekker

Reputation: 1

Hi There you might be able to use the below example to get back the result you require.

Select SUM(Inactive) Inactive ,SUM(Active) Active FROM
( 
    Select Count(t1.UserId) Inactive,0 Active 
    FROM
    (select * from users where inactive=1) as t1
    UNION 
    SELECT 0 Inactive,Count(t2.UserId) Active  FROM
    (select * from users where inactive=0) as t2  
) as result

Upvotes: 0

Related Questions