sql query implement two count in one

Question

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

ag_code!=0
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

What is the question? What results are you getting with the query you posted? — Galz
– Galz, Commented Mar 22, 2011 at 9:27
I really tried to focus on what exactly your query is doing, but I really couldn't grasp it. You'll need to describe in a little more detail what you're trying to accomplish here. — Michael McTiernan
– Michael McTiernan, Commented Mar 22, 2011 at 9:36

RichardTheKiwi · Accepted Answer · 2011-03-22 10:56:05Z

1

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

answered Mar 22, 2011 at 10:56

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mohan Over a year ago

Thanks its provided my expecting result exactly

RichardTheKiwi Over a year ago

@mohan - Great! Please tick next to the answer - you won't be able to do that yet for some more minutes

MatBailie Over a year ago

Is it better to explicitly state "else NULL", or beter to keep it short but implicit (and so not always understood)?

Andriy M Over a year ago

@Dems: There's a couple of middle ways, for example: keep it short and explain what it means or keep it short and let someone else explain what it means. :)

mohan Over a year ago

Hi again one query i need same before but the count want mcgross column based on 2types of condition 1.ag_code=0 2.ag_code!=0 SELECT tc.catename, count(case mcgross when u.ag_code!=0 then 1 end) agnt, count(case mcgross 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 I need count for mcgross based on the 2 condition 1.ag_code=0 2.ag_code!=0

RichardTheKiwi · Accepted Answer · 2011-03-22 10:53:26Z

0

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

edited Mar 22, 2011 at 10:53

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

answered Mar 22, 2011 at 9:49

Giovanni Bekker

1

Collectives™ on Stack Overflow

sql query implement two count in one

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related