Categories

Main
Database
Java
Microsoft.NET
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
ColdFusion
Crystal Reports
Delphi, C etc
JAVA
Microsoft.NET
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
XML & XSLT
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
Applications & Tools
Database Concepts & Design
EJB programming & troubleshooting
EJB design
General J2EE
XML & Web services
Web tier: servlets, JSP, Web frameworks
Performance and scalability
Industry news
TSS feedback
Mobicents Contributors
Mobicents Users
JSLEE Resource Adaptor Types
Planning JavaOne 2008
Sun Tech Days
Other Java conferences
Binary Web Services and XML
Metro and JAXB
GlassFish
GlassFish Plugins
Project jMaki
GlassFish WebTier
Mural
Java Development Tools
Java WS & XML Community News
JAXP
Java SE
6uN Early Access
Java Quick Starter
Java SE Snapshots: Project Feedback
JCK Forum
Feedback and Suggestions
JDK Distros
JDK Open Source
General JSR Discussion
JCP 2.6
JXTA Community Forum
ME Interest
ME Cool Apps
ME General Help
ME Feedback & Suggestions
ME Application Developer Interest
Blu-ray Disc Java
ME Developer Days
Squawk
Mobile Developer Alliance
OpenCable
LWUIT
JavaFX Script Language Discussion
OpenJFX General Discussion
Scene Graph
General Performance Discussion
Your Java Career
NetBeans 6.0
Servlets
JSP
JSF
Portals and Portlets
EJB and Other Java EE Technologies
Distributed Java
Object Relational Mapping
JDBC
Web Services
Swing / AWT / SWT / JFace
JNLP and Web Start
Java Micro Edition
Sockets and Internet Protocols
Threads and Synchronization
Performance
Applets
I/O and Streams
Other Java APIs
Game Development
Java in General (beginner)
Java in General (intermediate)
Java in General (advanced)
Programmer Certification (SCJP)
Developer Certification (SCJD)
Associate Certification (SCJA)
Web Component Certification (SCWCD)
EJB Certification (SCBCD)
Mobile Application Certification (SCMAD)
Architect Certification (SCEA)
Web Services Certification (SCDJWS)
XML Certification
Product and Other Certifications
Mock Exam Errata
Sun Certification Results
Authors' Corral
Book Reviews
Events
Bunkhouse Porch
Teachers' Lounge
Testing
OO, Patterns, UML and Refactoring
IDEs, Version Control and other tools
Ant, Maven and Other Build Tools
Linux / UNIX
Mac OS
HTML and JavaScript
XML and Related Technologies
Agile and Other Processes
General Computing
Security
Groovy
Scala
Other Languages
Struts
Application Frameworks
Other Open Source Projects
BEA/Weblogic
IBM/Websphere
Oracle/OAS
Apache/Tomcat
JBoss
Other Java Products and Servers
JavaRanch
Cattle Drive (java college)
Moderators Only
Trash Can
Jobs Offered
Jobs Wanted
Jobs Discussion
Meaningless Drivel
Programming Diversions
Blatant Advertising
Java Announcements
New To Java
Advanced Java
Java Applets
Networking
Threads and Synchronization
Java 2D
AWT / Swing
SWT / JFace
CLDC and MIDP
CDC and Personal Profile
Sun Java Wireless Toolkit
Enterprise JavaBeans
JavaServer Pages (JSP) and JSTL
Java Servlet
JavaServer Faces
Web Frameworks
Database
XML
Lucene
NetBeans
Eclipse
IntelliJ IDEA
JCreator
Other IDEs
Java Tutorials
Java Tips
Jobs Discussion
Jobs Offered
Jobs Wanted
Professional Certification
Forum Lobby
Java Blogs
Introductions
Reviews / Advertising
Suggestions & Feedback

Resources

Java Database
Linux
Coding
Mobile
Hardware
Software Development
Software Development
iOS,OS X
iOS,OS X
ORACLE
IBM DEVELOPER
IBM DEVELOPER
MSDN
MSDN


Tags

DB2

DB2 is IBM's offering to the highend database market. The latest version of DB2 (Universal Database) is ideal for OLTP, Data Warehousing, Decision Support and everything in between. It's well priced, extremely scalable and runs on virtually every platform out there from handhelds to mainframes.

multiple results in Single Query - How ??


My requirement is like the bellow.

I have a field called status which can only have "A" or "B" or "C" values.
Now I need a write a sql query which will select the count of records for the status "A" and count of records for "B" and for "C" but it should be a single sql query. 

Anyone give me how to do this!!!.

It is simple, use GROUP BY:

Select status,count(*) form mytable group by status order by status

HTH

Andy

But this will give a row wise result. But I need a column wise result!

You want one row returned?

Then it would be something like

select (select count(*) from mytable where ststus = 'A') as acount,
          (select count(*) from mytable where status = 'B') as bcount,
          (select count(*) from mytable where status = 'C') as ccount
from sysibm.sysdummy1

HTH

Andy


	Quote:
	
	
		
			
				Originally posted by antodomnic 
But this will give a row wise result. But I need a column wise result!

But this is not working for me. It is saying that 
"ILLEGAL SYMBOL COUNT. SOME SYMBOLS...". I am using db2 6.1 running under OS/390!.

Maybe if you gave us your SQL code, I could see what you are doing wrong.

Andy


	Quote:
	
	
		
			
				Originally posted by antodomnic 
But this is not working for me. It is saying that 
"ILLEGAL SYMBOL COUNT. SOME SYMBOLS...". I am using db2 6.1 running under OS/390!.

ok bellow is the actual query I am trying ,

SELECT (SELECT COUNT(*) FROM DB2PRD.HIRE_APLT A, DB2PRD.STORE B WHERE A.LOC_I = B.STORE_I AND A.LOC_TYPE_C = 'S' AND A.CUR_APLT_STAT_C IN ('HC')) as cur_aplt from from sysibm.sysdummy1




But I tried another simple sql query like select (select count(*) from db2prd.hire_aplt) as count from sysibm.sysdummy1 which is also not working!.

Try this:

select acnt, bcnt, ccnt
from 
  (select count(*) as acnt from mytable where status = 'A') as acount,
  (select count(*) as bcnt from mytable where status = 'B') as bcount,
  (select count(*) as ccnt from mytable where status = 'C') as ccount,
;

Ooops - take the last comma off.....

select acnt, bcnt, ccnt
from 
  (select count(*) as acnt from mytable where status = 'A') as acount,
  (select count(*) as bcnt from mytable where status = 'B') as bcount,
  (select count(*) as ccnt from mytable where status = 'C') as ccount
;

THAT WORKS PERFECTLY. THANK YOU VERY MUCH!!!!!!!!!!!!!!!!!!!!!!!

I do not know if this is acceptable syntax on 6.1 Os390, but on UDB 7.1 aix you can do something like

select 
    count(case when status='A' then 1 end) a
    count(case when status='B' then 1 end) b,
    count(case when status='B' then 1 end) c,
from mytable 
where status in ('A', 'B', 'C')

It should be faster then 3 separate sub-queries

regards,
dmitri

You are right, this would be faster and more efficent, but does not work on DB2/390, even on V7.1.
He has to use the solution mentioned before.

On /390, the count-column function can only use the asterisk or a distinct column-name.

Greetings Ingo


	Quote:
	
	
		
			
				Originally posted by chuzhoi 
I do not know if this is acceptable syntax on 6.1 Os390, but on UDB 7.1 aix you can do something like

select 
    count(case when status='A' then 1 end) a
    count(case when status='B' then 1 end) b,
    count(case when status='B' then 1 end) c,
from mytable 
where status in ('A', 'B', 'C')

It should be faster then 3 separate sub-queries

regards,
dmitri


Related Links

UDF unable to load Java class - Part II: What do I need to do to make this work?
prob with UDF
DB2 issue > db2fm
some doubts
want to backup only schemas
Problem : Long Time for closing a cursor
how to convert from db2 8.2 to 8.2.3 fixpack10?
Sqlstate=23505
CREATE TABLE DDL from system catalog
db2 fixpak installation
Covert UDF to a SQL Procedure
control center in unix, db2advis tool
optimizing the Delete SQL
select current timestamp
DB2 ODBC error
Foreign key relationship