[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Group By Caluse query Syntax

Posted on 2012-03-29
10
Medium Priority
?
313 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

'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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

656 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