MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SQL output using Groupby statement

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
vijayakumar
Beginner


Joined: 28 Jul 2006
Posts: 17
Topics: 9
PostPosted: Wed Jul 22, 2009 12:13 pm    Post subject: SQL output using Groupby statement Reply with quote
Hi,
I have a problem regarding SQL query, I am not getting the expected output, if the where condition is not satisfied the count should be displayed as zero. Could you please help me.

TABLE VALUES:
Code:

AC_NO       RLE_CD
A00R16      GOOD    
A00R16      BAD
A00R16      GOOD
B00R17      BAD
C00R19      GOOD
C00R19      BAD
D00R20      GOOD
D00R20      BAD
E00R22      GOOD
E00R22      BAD


sql is
Code:

  SELECT AC_NO, COUNT (*)
 FROM TABLEA WHERE AC_NO IN (
                    'A00R16',
                    'B00R17',
                    'C00R19',
                    'D00R20',
                    'E00R22')
       AND RLE_CD = 'GOOD'
GROUP BY AC_NO ;;

ACTUAL OUTPUT:
Code:

AC_NO  COUNT (*)
A00R16  2
C00R19  1
D00R20  1
E00R22  1


EXPECTED OUTPUT:
Code:

AC_NO  COUNT (*)
A00R16  2
B00R17  0
C00R19  1
D00R20  1
E00R22  1

Thanks in advance
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
PostPosted: Wed Jul 22, 2009 12:44 pm    Post subject: Reply with quote
vijayakumar,

Try this untested sql

Code:

SELECT AC_NO                                         
      ,SUM(CASE WHEN RLE_CD = 'GOOD'                 
                THEN INT(1) ELSE INT(0) END) AS COUNT
 FROM TABLE                                       
WHERE AC_NO IN ('A00R16',                             
                'B00R17',                             
                'C00R19',                             
                'D00R20',                             
                'E00R22')                             
GROUP BY AC_NO;
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vijayakumar
Beginner


Joined: 28 Jul 2006
Posts: 17
Topics: 9
PostPosted: Thu Jul 23, 2009 1:55 am    Post subject: Reply with quote
Kolusu,
I am getting the expected result with your query.

Thanks very much for you help.
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group