Select Question... [message #7065] |
Mon, 19 May 2003 13:58 |
Eric Rodriguez
Messages: 5 Registered: January 2003
|
Junior Member |
|
|
Is it possible to perform a select like this:
select count(*) AS rCount, FieldNameOne, FieldNameTwo
from tableName
If this is not possible, is there another way I can select row data and get a count of the rows at the same time?
|
|
|
Re: Select Question... [message #7066 is a reply to message #7065] |
Mon, 19 May 2003 14:23 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
quote from Eric Rodriguez:
----------------------------------------------------------------------
Is it possible to perform a select like this:
select count(*) AS rCount, FieldNameOne, FieldNameTwo
from tableName
If this is not possible, is there another way I can select row data and get a count of the rows at the same time?
----------------------------------------------------------------------
SQL> SELECT x
2 , y
3 , (SELECT COUNT(*) FROM t) total
4 FROM t
5 /
X Y TOTAL
- - ----------
A N 13
B O 13
C P 13
D Q 13
E R 13
F S 13
G T 13
H U 13
I V 13
J W 13
K X 13
L Y 13
M Z 13
13 rows selected.
SQL> Good luck,
A
|
|
|
Re: Select Question... [message #7073 is a reply to message #7066] |
Tue, 20 May 2003 00:47 |
robin baby
Messages: 13 Registered: May 2003
|
Junior Member |
|
|
Whenever u r selecting ,rows with its corresponding count u should use group by function,because count is a group function.you can use like this
SELECT x
2 , Y
3 , COUNT(Z) total
4 FROM t
5 GROUP BY X,Y;
INSTED OF USING
SELECT x
2 , y
3 , (SELECT COUNT(*) FROM t) total
4 FROM t
|
|
|
Re: Select Question... [message #7078 is a reply to message #7073] |
Tue, 20 May 2003 07:45 |
Martin Chadderton
Messages: 35 Registered: May 2003
|
Member |
|
|
Hi,
From 8.1.6, you can use the OLAP functionality, to achieve COUNTs without having to group by, i.e.
SQL> SELECT a, COUNT(*) OVER ()
2 FROM t;
A COUNT(*)OVER()
---------- --------------
X 2
X 2
Regards
M.
|
|
|
Re: Select Question... [message #7079 is a reply to message #7073] |
Tue, 20 May 2003 07:48 |
Martin Chadderton
Messages: 35 Registered: May 2003
|
Member |
|
|
Also, forgot to mention, that the GROUP BY mechanism proposed earlier is not logically identical to the originally proposed query, consider :
SQL> SELECT * FROM t;
A
----------
X
X
Y
SQL> select a, count(a)
2 from t
3 group by a;
A COUNT(A)
---------- ----------
X 2
Y 1
SQL> SELECT a, ( SELECT count(*) FROM t)
2 FROM t;
A (SELECTCOUNT(*)FROMT)
---------- ---------------------
X 3
X 3
Y 3
|
|
|