Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

COUNT(*) in DB2 to retrieve the count of each department

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vsrao_2k

New User


Joined: 12 Dec 2005
Posts: 13

PostPosted: Thu Oct 25, 2007 5:34 pm    Post subject: COUNT(*) in DB2 to retrieve the count of each department
Reply with quote

Hi All,

Help need to write a Query.

I am having 3 departments. DEPT1, DEPT2, DEPT3 for example.
dept1 haivng 10 records, DEPT2 having 20 records and dept3 haing 0

I want to retrieve the count of each department, if the count is zero, i want to display as 0.

select dept, (count(*),0) from Tablename
group by dept.

Can I write like this in DB2?

Thanks
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Oct 25, 2007 5:51 pm    Post subject: Re: COUNT(*) in DB2?
Reply with quote

vsrao_2k wrote:
Hi All,

Help need to write a Query.

I am having 3 departments. DEPT1, DEPT2, DEPT3 for example.
dept1 haivng 10 records, DEPT2 having 20 records and dept3 haing 0

I want to retrieve the count of each department, if the count is zero, i want to display as 0.

select dept, (count(*),0) from Tablename
group by dept.

Can I write like this in DB2?

Thanks


If the count is 0 how are you suppose to know that the department exists?
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Oct 25, 2007 11:07 pm    Post subject:
Reply with quote

To answer your question, the query looks correct acceptable, but Craq's question is outstanding.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Oct 25, 2007 11:49 pm    Post subject:
Reply with quote

Craq: If there is a department table with a child table with records and using a join, it could be possible....
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Fri Oct 26, 2007 2:01 pm    Post subject: Hi,
Reply with quote

Check it out with following query

SELECT DEPT, COUNT(*) GROUP BY DEPT
CASE
WHEN COUNT(*) = 0 THEN 0
WHEN COUNT(*) > 0 THEN COUNT(*)
.............
FROM TABLE

Try to execute and let me know the output

Raghav
Back to top
View user's profile Send private message
snehalpatel

New User


Joined: 13 Sep 2007
Posts: 37
Location: India

PostPosted: Fri Oct 26, 2007 3:10 pm    Post subject: Re: COUNT(*) in DB2 to retrieve the count of each department
Reply with quote

vsrao_2k wrote:
,

select dept, (count(*),0) from Tablename
group by dept.

Can I write like this in DB2?

Thanks


Hi

The Above Query gave me -104
Code:
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT   
         BE LEGAL ARE: + -       


We use Db2 version 7. Please let me know if it worked fine with you..
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Fri Oct 26, 2007 3:36 pm    Post subject: Hi vsrao_2k,
Reply with quote

Could u tell me Dept1, Dept2, Dept3 is a column or TABLE, send me the structure with some data...so that i can get back to you soon..

Raghav
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Oct 26, 2007 5:36 pm    Post subject:
Reply with quote

revel:

If DEPT3 isn't in the table, how does your query display that DEPT3 has a count of 0? You don't even know that DEPT3 exists because it has no entries on the table you are selecting from. Your query does not meet the requirements from the OP and your WHEN clauses don't do anything to change the results of the query.
Back to top
View user's profile Send private message
Ritesh Chopra

New User


Joined: 15 Oct 2007
Posts: 13
Location: India

PostPosted: Mon Nov 05, 2007 11:10 am    Post subject: Re: COUNT(*) in DB2 to retrieve the count of each department
Reply with quote

vsrao_2k wrote:
Hi All,

Help need to write a Query.

I am having 3 departments. DEPT1, DEPT2, DEPT3 for example.
dept1 haivng 10 records, DEPT2 having 20 records and dept3 haing 0

I want to retrieve the count of each department, if the count is zero, i want to display as 0.

select dept, (count(*),0) from Tablename
group by dept.

Can I write like this in DB2?

Thanks





If the DEPT1 ,2, 3 are the columns in a table, assuming they are column 1,2,3 for the table. please try out
select count(1), count(2), count(3) from ..............
try if it works.
expert please correct me if I am wrong.
Back to top
View user's profile Send private message
Ritesh Chopra

New User


Joined: 15 Oct 2007
Posts: 13
Location: India

PostPosted: Mon Nov 05, 2007 11:56 am    Post subject:
Reply with quote

you can ofcourse directly count the column by count(colname).
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Mon Nov 05, 2007 4:35 pm    Post subject:
Reply with quote

Hi vsrao_2k,

Sorry for late response,

According into my knowledge, its impossible to display COUNT as ZERO for a records whose entry is not present in a TABLE.
(I am assuming TABLE STRUCTURE like this

TABLE DEPARTMENT

Code:
DEPT
-------
dept1
dept2
dept1
dept1
------- 


Since there is no entry of DEPT3 in a TABLE, we cann't count DEPT3 tuples in a TABLE.

Any suggestion is welcomed

Regards,
Raghu
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Nov 05, 2007 9:15 pm    Post subject:
Reply with quote

Hello,

Is there also a "dept table" table that has one row per defined dept (not info about people in the dept, just reference info about the dept)?

If there is, this reference table might be joined with the table that may have multiple rows per department and zero counts for depts with no rows could be shown.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Display FTP Session Count within TSO Yolanda Harvey TSO/ISPF 3 Fri Mar 10, 2017 10:31 pm
No new posts Count Trailing Spaces in variable str... Virendra Shambharkar SYNCSORT 10 Thu Feb 02, 2017 12:23 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us