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.
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.
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 .
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'
JOINsyntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged