Link to home
Start Free TrialLog in
Avatar of asphaltninja
asphaltninjaFlag for United States of America

asked on

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

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?

Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Try this
ALTER PROCEDURE getContCollecinfo
@plnCode varchar(10)

AS

SELECT a.ssn, a.lname,a.fname, a.cont, a.comp, 
       (select count(1) 
        from cont_collec_messagetype
        JOIN contMessages ON contMessages.SSN = a.SSN 
        where messCode = b.messCode
        and IsError = 1
        ) Errors
from cont_collec_payroll_info a 
where a.plnCode=@plnCode

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of asphaltninja

ASKER

Worked perfectly, thanks!