What's wrong...?

[Jamie Collins]

....with the following Access/Jet SQL?

CREATE TABLE Test (data_col INTEGER)
;
INSERT INTO Test (data_col) VALUES (NULL)
;
SELECT COUNT(SELECT COUNT(*)
FROM Test AS T2
WHERE T2.data_col = Test.data_col
) AS tally
FROM Test
;

Very amusing but not much help, I suspect.

Jamie.

--

 

[Terry Kreft]

You're final result is 0, which is correct.

It's not very funny though.

You would get the same result with
SELECT COUNT(*) AS tally
FROM Test
INNER JOIN Test AS T2
ON T2.data_col = Test.data_col

Which again is correct.

 

[Jamie Collins]

You're final result is 0, which is correct.

It's not very funny though.

Try this version, it may tickle you more:

SELECT CDec(SELECT COUNT(*)
FROM Test AS T2
WHERE T2.data_col = Test.data_col
) AS tally
FROM Test
;

Jamie.

--

 

[Jamie Collins]

You would get the same result with
SELECT COUNT(*) AS tally
FROM Test
INNER JOIN Test AS T2
ON T2.data_col = Test.data_col

That does not hold true for a table containing non-distinct values e.g.

CREATE TABLE Test (data_col INTEGER)
;
INSERT INTO Test (data_col) VALUES (1)
;
INSERT INTO Test (data_col) VALUES (2)
;
SELECT COUNT(SELECT COUNT(*)
FROM Test AS T2
WHERE T2.data_col = Test.data_col
) AS tally
FROM Test
;

Jamie.

--

 

[Jamie Collins]

You would get the same result with <<snip>>

As a further hint, equivalent constructs would include the use of the
COUNTER function:

SELECT COUNTER(SELECT COUNT(*)
FROM Test AS T2
WHERE T2.data_col = Test.data_col
) AS tally
FROM Test;

or even the more well known TALLY function:

SELECT TALLY(SELECT COUNT(*)
FROM Test AS T2
WHERE T2.data_col = Test.data_col
) AS tally
FROM Test;

tongue firmly embedded in cheek, naturally.

Jamie.

--

 

[Terry Kreft]

So 0 (the correct result) again.

 

[Terry Kreft]

That's true , this is slightly amusing but understandable, as ...

SELECT (SELECT COUNT(*)
FROM Test AS T2
WHERE T2.data_col = Test.data_col
) AS tally
FROM Test

.... gives the same result.

Try
CREATE TABLE Test (data_col INTEGER)
;
INSERT INTO Test (data_col) VALUES (1)
;
INSERT INTO Test (data_col) VALUES (2)
;
INSERT INTO Test (data_col) VALUES (3)
;
SELECT COUNT(SELECT COUNT(*)
FROM Test AS T2
WHERE T2.data_col = Test.data_col
) AS tally
FROM Test

and compare with
CREATE TABLE Test (data_col INTEGER)
;
INSERT INTO Test (data_col) VALUES (1)
;
INSERT INTO Test (data_col) VALUES (2)
;
INSERT INTO Test (data_col) VALUES (3)
;
INSERT INTO Test (data_col) VALUES (3)
;
SELECT COUNT(SELECT COUNT(*)
FROM Test AS T2
WHERE T2.data_col = Test.data_col
) AS tally
FROM Test

 

[Jamie Collins]

That's true , this is slightly amusing

Just in case this is still confusing anyone, there seems to be a bug
with the SQL parser as regards the text before the subquery. For
example, the following code causes no error:

SELECT data_col, anything_you_like_here
(SELECT COUNT(*)
FROM Test AS T2
)
FROM Test

Could this be exploited e.g. to comment SQL code?

CREATE VIEW MyView
AS
SELECT data_col,
this_returns_a_scalar (SELECT COUNT(*)
FROM Test AS T2
)
FROM Test;

Jamie.

--