Count * with alias

Transact-SQL
1

Hi

Why is it when I do:

select count(t.*)
from table t LEFT JOIN table x
ON t.ID = x.ID

It underlines the * section saying incorrect syntax. Can you not use * with alias. I'm sure this worked before?

Thanks

Andrew

2

Count does not count all the columns, it counts rows in one column or all the rows or groups of rows. When you say count(t. *) you're telling sql to count all the columns in the table t.

3

Maybe it would help if I explained the problem. I have a query like in first post

select count(t.*)
from table t LEFT JOIN table x
ON t.ID = x.ID

And say the left joined table (table1) has 1000 rows and the other table has 200 then I would always expect to return a count of rows 1000 as all rows from table 1 would be returned regardless of a match with other table, due to left join. But in this case I am getting more rows returned when I run the query, more than 1000, which is confusing me. I was trying to run the query but do a count on only the rows from the left table to see if I got 1000. Not sure if this would be the way to do it.

Hopefully that kind of makes sense?

4

Joins are used to query data from two or more tables. A Left Join will bring you rows from the left and right that mach the criteria t. Id=X. Id and rows from the left that doesn't Mach the criteria so if there is a mach with the right table that row will be counted. You can use distinct to remove the duplicates from the result like. Count(distinct x.id)

5

An alias/table prefix is not allowed in a COUNT(*). It is allowed in a SELECT list. That is, you cannot do this:

SELECT COUNT(t.)
do this instead:
SELECT COUNT()

but you can do this:

SELECT t., c.

6

If you use COUNT(*) you will get a total that is, for sure, 1000 rows but ADD to that any rows from the LEFT JOIN Table2. If there is only ever one row from Table2 matching a row in Table1 you will still get a result of 1000, but if, for example, all 200 rows in Table2 matched the first row in Table1 then the result would be 1199.

You can do

SELECT COUNT(DISTINCT T1.ID)
FROM Table1 AS T1
    LEFT JOIN Table2 AS T2
       ON T2.ID = T1.ID

and that would give you 1000, regardless of how many rows there were in Table2. In that example it might be instructive to try:

SELECT [Count]=COUNT(*), [T1]=COUNT(DISTINCT T1.ID), [T2]=COUNT(DISTINCT T2.ID)
...

Note that if there are any NULL values for T1.ID or T2.ID (e.g. because of the outer join) you will get a warning message - and those warning messages can muck about with APPs that process the data.