Solved

Group By Caluse query Syntax

Posted on 2012-03-29
10
306 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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