COUNT(1) , returns zero records?

[1DMF]

Hi

I'm struggling with why my SQL command won't work.

If i run

SELECT FirstName + ' ' + LastName AS Adviser,CompanyName FROM Business_Register LEFT JOIN Members ON Business_Register.Adv_MemNo = Members.ID WHERE  Adv_MemNo like 'n%'

I get 23140 records!

However, as soon as I try to group and count with..

SELECT FirstName + ' ' + LastName AS Adviser,CompanyName,COUNT(1) AS Cases FROM Business_Register LEFT JOIN Members ON Business_Register.Adv_MemNo = Members.ID WHERE Adv_MemNo like 'n%'  GROUP BY Adviser,CompanyName

I get 0 records, what am i doing wrong?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

It's OK, worked it out , I cannot group by adviser, I have to group using the concatenation.

---------------------------------------------------
Word of warning , NEVER, EVER user the DBI module in PERL with MS SQL no matter what anyone tells you, it sucks, doesn't work properly and doesn't report errors be them syntax or otherwise!

Use WIN32::ODBC everytime!



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[gmmastros]

Which table is Adv_MemNo in?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Business_Register

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

Just as an example, I replaced the DBI module with Win32::ODBC and now I get errors such as

xxxxx is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Which DBI doesn't seem to produce?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts