IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

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


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
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
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
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
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

Moderator Emeritus


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

PostPosted: Mon Nov 05, 2007 9:15 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts To get the count of rows for every 1 ... DB2 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Validating record count of a file is ... DFSORT/ICETOOL 13
No new posts Using Java/C/C++ to retrieve dataset ... Java & MQSeries 6
No new posts Insert header record with record coun... DFSORT/ICETOOL 14
Search our Forums:

Back to Top