CODEWITHSUNDEEP
sqlsql-server
rstruck
rstruck

Reputation: 1294

Get count for different tables in the same query

What is wrong with:

select count (a.*, b.*)
from tableA a, tableB b
where a.x = 'blah'
and b.x = 'blah'

I keep getting the error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.

I have a bunch of table names to select from so wanted to use table name aliases? Any help appreciated: I'm not very good with sql.

Upvotes: 0

Views: 16148

Answers (3)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

You cannot use multiply arguments in COUNT. In your case you are asking to give you count of a.col1, a.col2, a.col3... COUNT expects just 1 argument. The only exception is * in that case it understands that it is supposed to calculate all the records from the table.

If you would like to calculate all the records from a, then do something like COUNT(a.id). If you want to calculate unique records of a table, then do COUNT(distinct a.id).

If you want to calculate all the records from a and b tables, then you can do COUNT(a.id) as quantityOfAtable, COUNT(b.id) as quantityOfBtable. Or you can sum them COUNT(a.id) + COUNT(b.id).

One more thing you need to know about COUNT that it is counting only the NOT NULL rows. So, if you are using LEFT JOIN or the column that can be NULL then it will just calculate the amount of the records where this column is NOT NULL. Of course, you can use DISTINCT to calculate unique records as well.

Upvotes: 5

Brett Green
Brett Green

Reputation: 3765

You should be using JOIN syntax here:

select count(*)
from tableA a
inner join tableB b
  on a.x = b.x
where a.x = 'blah'

Upvotes: 0

void
void

Reputation: 7890

  • COUNT() function takes only one parameter.

count (a.*): means count(a.field1,a.field2,a.field3,....) which is invalid.

you have to use count(*): means count all .

Upvotes: 0

Related Questions