sql count() and *

Is this possible in sql :

COUNT(ads.id) AS ads, *

If not, then what to use? I'm using LEFT JOIN, there are two tables: ads and ad, but I'm not using GROUP BY:

SELECT COUNT(ads.id) AS ads_count, 
       ads.* 
  FROM ...

It's not working.

It's certainly possible to include a * in the SELECT list alongside other columns, in general. But COUNT() is an aggregate function, and the implication there is that you're grouping by every other column in the resultset, which is probably not true.

Whether or not that query will function may be heavily dependent on which DBMS you're using, which you haven't specified. In MS SQL Server, you must declare all non-aggregate columns in a GROUP BY clause, and * is not a valid member in a GROUP BY clause, hence in SQL Server that's an invalid query.

MySQL seems to have somewhat looser rules around grouping and using aggregate functions, so it's possible that query may be syntactically valid (I don't have a MySQL database handy to test it), but its results would almost certainly be indeterminate...

You could do something like this:

-- test table
declare @T table(name varchar(10), number int)

select *, count(name)
from @T 
group by number, name

In MSSQL, if you select * then you would have to list all of the columns in the group by.

Of course the only counts that would be greater then 1 would be for duplicated rows.

This should work:

SELECT COUNT(ads.id) AS ads_count, 
       ads2.*
FROM table_name ads
JOIN table_name ads2
GROUP BY ads.id

table_name should be your table name.