|
View previous topic :: View next topic
|
| Author |
Message |
rups
New User
Joined: 19 Jan 2006
Posts: 17
Location: bangalore
|
|
|
|
hi,
i need to get counts of all columns in a table having value > space.
can i get it using single query?if yes,please let me know the query syntax.
thanks
rups |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hellp Rups,
Your query is not very clear.
Do you want to find number of rows having value greater than space for particular column? |
|
| Back to top |
|
|
rups
New User
Joined: 19 Jan 2006
Posts: 17
Location: bangalore
|
|
|
|
hello guptae,
yes i want to find total no of rows having value greater than space of all the column present in a table.
suppose if i have a table just like below
emp-id name
1 xx
2
3 yy
Query should give me answer following answer
emp-id = 3(since all the three rows has data)
name=2(only two rows has data)
i want to get the above result by executing single query.
thanks
rups |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Try
| Code: |
SELECT COUNT(EMPID),COUNT(NAME) FROM TABLE WHERE EMPID > '' OR NAME > '' ;
|
not tested ... |
|
| Back to top |
|
|
rups
New User
Joined: 19 Jan 2006
Posts: 17
Location: bangalore
|
|
|
|
hi ashimer,
i tried your query,but i got the answer of emp-id count as 3 and name as 3.
thanks
rups |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
Try
| Code: |
SELECT COUNT(empid)
,(SELECT COUNT(name)
FROM table1
WHERE name > ' '
)
FROM table1
WHERE empid > ' '
|
|
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hello,
| Quote: |
| tried your query,but i got the answer of emp-id count as 3 and name as 3. |
And what did you expect ? Your table definition is not known to us so no one (me) can not comment much on your this post. |
|
| Back to top |
|
|
rups
New User
Joined: 19 Jan 2006
Posts: 17
Location: bangalore
|
|
|
|
hello anuj,
here is the structure of the table
Emp-id Name
1 xx
2
3 yy
emp-id is defined as char(3) NOT NULL
name as char(10) NOT NULL |
|
| Back to top |
|
|
murugan_mf
Active User
.jpg)
Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India
|
|
|
|
| Code: |
SELECT 'EMPID', COUNT(EMP-ID)
FROM EMPTAB WHERE EMP-ID<>SPACE(3)
UNION
SELECT 'EMPNAME',COUNT(NAME)
FROM EMPTAB WHERE NAME<>SPACE(10); |
|
|
| Back to top |
|
|
|
|
