count(*) and group by causes errror

[mwlyoung]

Whenever I execute the following query:select member_id,
company.company_code,
company.credit_limit
system,
member_id,
first_name,
last_name,
members.credit_limit,
members.email,
date_sent,
count(*)
from Company, Members
left outer join Reminders on members.email = reminders.email
group by member_id

I get the following error message:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Company.company_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

...and so forth for each field in the select list.

Any ideas?

 

[MeanGreen]

With your group by you have to list all non aggregate columns:

select member_id,
company.company_code,
company.credit_limit
system,
member_id,
first_name,
last_name,
members.credit_limit,
members.email,
date_sent,
count(*)
from Company, Members
left outer join Reminders on members.email = reminders.email
group by member_id,company.company_code,
company.credit_limit
system,
member_id,
first_name,
last_name,
members.credit_limit,
members.email,
date_sent

Hope this helps.

 

[topher0303]

After you apply MeanGreen's fix, you still may have a problem. You did not specify a join for the company table. This will cause it to default to a FULL OUTER JOIN. However, if that is what you meant for it to do, then I guess it's not a problem after all.

 

[mwlyoung]

But I don't actually want to group the table by all the fields, otherwise it's useless if I want to add totals by member and by company.

How will I do that?

 

[tonylnng]

Assuming that your join condition is correct and you can try to use following SQL statement.

I didn't put the Reminder to sub-query and just put it into if it is necessary.

select
member_id,
company.company_code,
company.credit_limit system,
member_id,
first_name,
last_name,
members.credit_limit,
members.email,
date_sent,
vwCount.total
from Company, Members
left outer join Reminders on members.email = reminders.email
left outer join (select company.company_code, count(member_id) total from Company, Members group by company.company_code) vwCount

Please correct if I am wrong.

 

[mwlyoung]

Just a question about this though. What is in the view vw_Count in the last line your joining the view, but on which column?

Thanks

Louis

 

[tonylnng]

Sorry, you should join as follow

select
member_id,
company.company_code,
company.credit_limit system,
member_id,
first_name,
last_name,
members.credit_limit,
members.email,
date_sent,
vwCount.total
from Company, Members
left outer join Reminders on members.email = reminders.email
left outer join (select company.company_code, count(member_id) total from Company, Members group by company.company_code) vwCount on Company.company_code = vwCount.company_code

Then you may have the total of member at each record that determined by company

 

[mwlyoung]

Just one last question. What does the code look like for the view vw_Count?

Sorry if I'm pestering, but I'm quite a newbie at SQL.

Thanks

 

[alfredp77]

the vwcount is not a view, it is an alias for the query:

select company.company_code, count(member_id) total from Company, Members group by company.company_code

 

[tonylnng]

Sorry, I am not really understand your question. Would you please explain more detail?

 

[tonylnng]

Thanks, alfredp77!