Group By Caluse query Syntax

I have one table EMP  like this.

Dept empid Higher
IT 1 yes
HR 2 Yes
IT 3 No
IT 4 No
HR 5 Yes
HR 6 No

My query is "Select Dept, Count(empid) as Total group by Dept"
Out put is
IT 3
HR 3

I want to show the output in other 2 columns as count of Yes and count of No

Please can you help with this.
AnandSahooAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MilleniumaireCommented:
If you are using Oracle, you can do the following:

Select Dept,
          Count(empid) as Total,
          Sum(decode(Higher,'Yes',1,0)) as Count_Yes,
          Sum(decode(Higher,'No',1,0)) as Count_No
group by Dept
0
MilleniumaireCommented:
Sorry, as in your question I missed out the from clause....

Select Dept,
          Count(empid) as Total,
          Sum(decode(Higher,'Yes',1,0)) as Count_Yes,
          Sum(decode(Higher,'No',1,0)) as Count_No
From emp
group by Dept
0
sdstuberCommented:
this should work in sqlserver, oracle, mysql, db2

SELECT   dept,
         COUNT(empid) AS total,
         SUM(CASE WHEN higher = 'Yes' THEN 1 END) AS count_yes,
         SUM(CASE WHEN higher = 'No' THEN 1 END) AS count_no
    FROM emp
GROUP BY dept
ORDER BY dept DESC
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

gpizzutoCommented:
@sdstuber: good!

Better:

SELECT   dept,
         COUNT(empid) AS total,
         SUM(CASE WHEN higher = 'Yes' THEN 1 ELSE 0 END) AS count_yes,
         SUM(CASE WHEN higher = 'No' THEN 1 ELSE 0 END) AS count_no
    FROM emp
GROUP BY dept
ORDER BY dept DESC
0
sdstuberCommented:
why is that better?

I count 1 when it's yes/no

if it's not, then the case will be null and hence not be counted

the sum will work, but is neither better nor worse.  
semantically I think count makes more sense, but functionally they are equivalent in this usage.  I prefer count for "counting", and sum for "adding".
0
AnandSahooAuthor Commented:
hI sdstuber,

That perfectly works when I run it in query analyzer Toad and Sql server.
Same gives error when I put it on asp.net page to query from oracle database.

Any thoughts ?
0
sdstuberCommented:
what is the error?
0
AnandSahooAuthor Commented:
Server Error in '/' Application.
--------------------------------------------------------------------------------

IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
0
sdstuberCommented:
Unless there is an underlying oracle error somewhere in your stack trace I don't think I'll be able to suggest much for that.

Perhaps open a new question in the asp.net topic areas.


To rule out the query  itself as the problem try using a stub

SELECT   'test' dept,
         3 AS total,
         2 AS count_yes,
         1 AS count_no
    FROM dual;
0
AnandSahooAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.