?
Solved

Group By Caluse query Syntax

Posted on 2012-03-29
10
Medium Priority
?
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 74

Accepted Solution

by:
sdstuber earned 1500 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
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 
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 74

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
 

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 74

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 74

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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

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…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

801 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