Solved

Group By Caluse query Syntax

Posted on 2012-03-29
10
301 Views
Last Modified: 2012-06-28
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.
0
Comment
Question by:AnandSahoo
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 37782453
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
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 37782460
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37782565
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
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37782620
@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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37782714
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:AnandSahoo
ID: 37783057
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37783070
what is the error?
0
 

Author Comment

by:AnandSahoo
ID: 37783238
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37783281
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
 

Author Closing Comment

by:AnandSahoo
ID: 37783487
thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now