Simple count * issue

[AgentM]

Hi,
I am trying to do the following query and I get a Syntax error

Query: SELECT COUNT[D1.*) AS [Col1] FROM Database1 AS D1
INNER JOIN Database2 AS D2 ON D1.CoL1 = D2.CoL1

Error: incorrect syntax near '*'

What am I missing.

Thank you.

 

[markros]

You used [ instead of (

e.g. select count(*) from ...

 

[AgentM]

Here's the modified query minus the '[' bracket

Query: SELECT COUNT(D1.*) AS [Col1] FROM Database1 AS D1
INNER JOIN Database2 AS D2 ON D1.CoL1 = D2.CoL1

 

[gmmastros]

looks like you have a square bracket where you really want a parenthesis.

SELECT COUNT[!]([/!]D1.*) AS [Col1] FROM Database1 AS D1
INNER JOIN Database2 AS D2 ON D1.CoL1 = D2.CoL1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AgentM]

'[' was a typo. pls ignore.

Query: SELECT COUNT(D1.*) AS [Col1] FROM Database1 AS D1
INNER JOIN Database2 AS D2 ON D1.CoL1 = D2.CoL1

Same error.

thank you.

 

[gmmastros]

The count from D1 is guaranteed to be the same as any other count, so...

SELECT COUNT(*) AS [Col1] FROM Database1 AS D1
INNER JOIN Database2 AS D2 ON D1.CoL1 = D2.CoL1

You could also write this a dozen other ways:

SELECT COUNT(1) AS [Col1] FROM Database1 AS D1
INNER JOIN Database2 AS D2 ON D1.CoL1 = D2.CoL1

SELECT COUNT(1) AS [Col1] FROM Database1 INNER JOIN Database2 ON Database1.CoL1 = Database2.CoL1

If you want to know how many distinct rows there are with D1, then you could even do this...

SELECT COUNT([!]Distinct D1.PrimaryKeyColumn[/!]) AS [Col1] FROM Database1 AS D1
INNER JOIN Database2 AS D2 ON D1.CoL1 = D2.CoL1

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom