View previous topic :: View next topic
|
Author |
Message |
vsrao_2k
New User
Joined: 12 Dec 2005 Posts: 13
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
To answer your question, the query looks correct acceptable, but Craq's question is outstanding. |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
Craq: If there is a department table with a child table with records and using a join, it could be possible.... |
|
Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005 Posts: 135 Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
|
snehalpatel
New User
Joined: 13 Sep 2007 Posts: 37 Location: India
|
|
|
|
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 |
|
|
revel
Active User
Joined: 05 Apr 2005 Posts: 135 Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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 |
|
|
Ritesh Chopra
New User
Joined: 15 Oct 2007 Posts: 13 Location: India
|
|
|
|
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 |
|
|
Ritesh Chopra
New User
Joined: 15 Oct 2007 Posts: 13 Location: India
|
|
|
|
you can ofcourse directly count the column by count(colname). |
|
Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005 Posts: 135 Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|