We help IT Professionals succeed at work.
Get Started

SQL Join using temp table that also has count totals, am I doing this right?

373 Views
Last Modified: 2012-05-11
So I have one table that stores information about employees, and another table that stores information about errors associated with each user.

What I need to do is retrieve a dataset that holds the user's information in the first 5 fields, and then add an 'error' column at the end that's a sum of the user's errors from the Errors table. The schema is (roughly) as follows:

Employee table:
SSN varchar (primary key)
plnCode varchar,
FirstName varchar,
LastName varchar,
Cont int,
Comp int

Errors Table:
ErrorCode int(Primary key),
SSN varchar(foriegn key)
isCritical bit

Here's what I have so far to retieeve the data:

ALTER PROCEDURE getContCollecinfo
@plnCode varchar(10)

AS
DECLARE @ssn varchar(12)

SELECT a.ssn, a.lname,a.fname, a.cont, a.comp
into #tempa from cont_collec_payroll_info a 
JOIN contMessages b ON
a.SSN = b.SSN
JOIN cont_Collec_MessageType c ON
b.messCode = c.messCode
where a.plnCode=@plnCode and c.IsError = 1

alter table #tempa
add Errors int

update #tempa
SET Errors = count(d.isError) from cont_collec_messagetype d
join contmessages e on 
d.messCode = e.messCode
join #tempa f on
e.ssn = f.ssn
where d.isError = 1 and f.ssn = e.ssn

select * from #tempa

drop table #tempa

Open in new window


I get an "An aggregate may not appear in the set list of an UPDATE statement." error when I try to execute. What am I doing wrong?

Comment
Watch Question
Software Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE