Issue of COUNT(*)

Question

Post reply

Issue of COUNT(*)

SQL ORACLE

One Orange Chip

Points: 27807
More actions
August 11, 2010 at 5:26 pm

#222426

The following script works
SELECT COUNT(*) FROM TableA
But the following scripts do not work
SELECT COUNT(TableA.*) FROM TableA
SELECT COUNT(a.*) FROM TableA a
Any idea, why?
Any input will be greatly appreciated in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

FabricioLimaDBA Mr or Mrs. 500 Points: 507 More actions · Answer 1

You can't do this:

SELECT COUNT(TableA.*) FROM TableA

SELECT COUNT(a.*) FROM TableA a

You can only put a column: select COUNT(Nome_Coluna) from TableA

Fabrício França Lima
MCITP – Database Administrator
https://www.fabriciolima.net/Blog

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 2

It's worth thinking about what you're counting. In your first (working) example, you are counting all rows that are in the results from SELECT FROM TableA. This will give you a could of all rows in A.

In your non-working examples, you're still using COUNT() over a set of results. A result set is always square - that is, you'll have some value (or a null) for every column being returned. So it doesn't make sense to count the number of rows just from TableA.

If you perform a join, and want to know how many values are in A, you can pick a non-nullable column from A, and count this column.

A long example script:

SET NOCOUNT ON

CREATE TABLE #A (a int)

CREATE TABLE #B (a int, b varchar(20))

GO

DECLARE @i int

SET @i = 1

WHILE @i < 50

BEGIN

INSERT INTO #A (a) VALUES (@i)

IF @i % 3 = 0

INSERT INTO #B (a, b) VALUES (@i, 'A factor of 3!')

ELSE

INSERT INTO #B (a, b) VALUES (@i, null)

SET @i = @i + 1

END

SELECT * FROM #A -- 49 rows, 1 through 49

SELECT * FROM #B -- 49 rows, a = 1 through 49, every third b has "A factor of 3!"

SELECT COUNT(*) FROM #A -- 49

SELECT COUNT(*) FROM #B -- 49

SELECT COUNT(a) FROM #A -- 49

SELECT COUNT(a) FROM #B -- 49

SELECT COUNT(b) FROM #B -- 16 (doesn't count null values!)

SELECT COUNT(*) FROM #A LEFT JOIN #B on #A.a = #B.a -- 49

SELECT COUNT(#A.a) FROM #A LEFT JOIN #B on #A.a = #B.a -- 49

SELECT COUNT(#B.a) FROM #A LEFT JOIN #B on #A.a = #B.a -- 49

SELECT COUNT(#B.b) FROM #A LEFT JOIN #B on #A.a = #B.a -- 16

-- Delete a number of B rows.

DELETE FROM #B WHERE b IS NULL

SELECT COUNT(*) FROM #A LEFT JOIN #B on #A.a = #B.a -- 49

SELECT COUNT(#A.a) FROM #A LEFT JOIN #B on #A.a = #B.a -- 49

SELECT COUNT(#B.a) FROM #A LEFT JOIN #B on #A.a = #B.a -- 16

SELECT COUNT(#B.b) FROM #A LEFT JOIN #B on #A.a = #B.a -- 16

DROP TABLE #A

DROP TABLE #B

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

I came across the following issue:

The following scripts give me correct returns:

SELECT COUNT(a.colA) FROM TableA a

SELECT COUNT(b.colA) FROM TableB b

However, the script below is syntax correct, but returns incorrect result.

SELECT COUNT(a.colA), COUNT(b.colB) FROM TableA a, TableB b

Any idea to explain it?

Any input will be greatly appreciated in advance.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

SQL ORACLE (8/13/2010)
I came across the following issue:
The following scripts give me correct returns:
SELECT COUNT(a.colA) FROM TableA a
SELECT COUNT(b.colA) FROM TableB b
However, the script below is syntax correct, but returns incorrect result.
SELECT COUNT(a.colA), COUNT(b.colB) FROM TableA a, TableB b
Any idea to explain it?
Any input will be greatly appreciated in advance.

The two are completely different.

The first statements are two separate queries each querying a single table.

The second query generates the cartesian product of the two tables. The size of a cartesian product resultset is the number of rows in the first table multiplied by the number of rows in the second table. It is equivalent to:

SELECT COUNT(a.colA), COUNT(b.colB) FROM TableA a CROSS JOIN TableB b

If you want the output from the first two queries in a single resultset, then you can do something like this:

SELECT 'A' AS TableId, COUNT(colA) AS TableRowCount FROM TableA

UNION ALL

SELECT 'B', COUNT(colA) FROM TableB

If you want the individual table counts in a single row, then you can do this:

SELECT (SELECT COUNT(colA) FROM TableA) AS A,

(SELECT COUNT(colB) FROM TableB) AS B

Also, I notice you are counting colA in TableB in the first set of statements and colB in TableB in the cartesian product query, which (if it isn't a typo) could produce different results if the columns are nullable.