Group by and Join causes error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by and Join causes error

Author

Topic

gretty
Starting Member

6 Posts

Posted - 2009-10-25 : 00:31:36

Hello

my objective is to do the below but I get an error because of my group by statement??

quote:
Display each rep number and name and their total number of customers

The tables involved are:
Customer:

quote:

CUSTOMER_NUM PRIMARY KEY,
CUSTOMER_NAME
REP_NUM CHAR

Rep

quote:

REP_NUM PRIMARY KEY,
LAST_NAME
FIRST_NAME
STREET
CITY
STATE
ZIP
COMMISSION

select last_name,first_name, rep.rep_num, count(*)
from customer C,rep R
where C.rep_num = R.rep_num
group by R.rep_num;

winterh
Posting Yak Master

127 Posts

Posted - 2009-10-25 : 08:24:59

WHat you had is a ommon mistake made by beginners, you can't just choose two tables to use in the FROM Statement, you must 'Join' them on a UniqueIDentifier or something common to both tables. WHat I have done should give you some Idea of how to use it.

select r.rep_num, r.First_Name, r.Last_Name, c. count(*)
from customer C
Inner Join Rep r on r.Rep_num = c.Rep_num
where C.rep_num = R.rep_num
group by R.rep_num, r.First_Name, r.Last_Name
Order By r.Rep_num

Hope this helps.

[/fail at query]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources