Aggregate problem

I am getting the following error in my SQL.
Column 'dbo.ClientWorkerStatus.WorkerTypeID' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
My code is below. What I want to get is up to 1, 2 or 3 counts for one
ClientID as there are multiples in the ClientWorkerStatus table.
SELECT dbo.ClientWorkerStatus.ClientID,
Preferred = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 0 THEN 1
ELSE 0
END,
Family = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 1 THEN 1
ELSE 0
END,
Pool = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 2 THEN 1
ELSE 0
END
FROM dbo.ClientWorkerStatus INNER JOIN
dbo.ClientWorkerLink ON dbo.ClientWorkerStatus.ClientID =
dbo.ClientWorkerLink.ClientID AND
dbo.ClientWorkerStatus.WorkerID = dbo.ClientWorkerLink.WorkerID
WHERE (dbo.ClientWorkerStatus.StatusEnd IS NULL OR
dbo.ClientWorkerStatus.StatusEnd > GETDATE()) AND
(dbo.ClientWorkerLink.Active = 1)
GROUP BY dbo.ClientWorkerStatus.ClientID
Thanks, DavidWhat aggregate?
Have you tried something like:
SELECT s.ClientID,
Preferred = CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
Family = CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
Pool = CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
FROM
dbo.ClientWorkerStatus s
INNER JOIN dbo.ClientWorkerLink l
ON s.ClientID = l.ClientID
AND s.WorkerID = l.WorkerID
WHERE
COALESCE(s.StatusEnd, '20300101') > GETDATE()
AND l.Active = 1
GROUP BY
s.ClientID,
CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
Without DDL, sample data and desired results, this is only a guess. See
http://www.aspfaq.com/5006 for info on providing details that will yield a
full solution.|||Hello, David
Is the ClientI=ADD column the primary key (or a unique key) in the
ClientWorkerStatus table ? If yes, you can safely add the
WorkerT=ADypeID column to the GROUP BY list, i.e:
SELECT [...]
GROUP BY dbo.ClientWorkerStatus.ClientI=ADD,
dbo.ClientWorkerStatus.WorkerT=ADypeID=20
Razvan|||Erase your group by at the end of your query.
You don't have any sum, avg, .... in your query. The group by clause is not
required.
Jonathan
"Razvan Socol" wrote:

> Hello, David
> Is the ClientI_D column the primary key (or a unique key) in the
> ClientWorkerStatus table ? If yes, you can safely add the
> WorkerT_ypeID column to the GROUP BY list, i.e:
> SELECT [...]
> GROUP BY dbo.ClientWorkerStatus.ClientI_D,
> dbo.ClientWorkerStatus.WorkerT_ypeID
> Razvan
>|||Yes, Aaron...your's worked. However I was also able to get it to work
by surrounding the CASE statements in the SELECT clause by SUM(...).
For example:
Preferred = SUM(CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END), ...etc
I do want to group the results by ClientID because any one ClientID can
have more than 1 WorkerTypeID so I need the count of each.
Thanks.
David
*** Sent via Developersdex http://www.examnotes.net ***|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You have absolutely no idea how to design a schema, do you?
You have columns named "-type_id" which make no sense. Look at the
ISO-11179 Standards.
You have table names that end with "-link", as if you were writing a
1970's navigation database.
You have table of status codes when status is a kind of attribute, not
an entity.
Then after all those FUNDAMENTAL errors, you do not have any aggregates
in the useless crap you posted.
dbo.ClientWorkerStatus.StatusE=ADnd > GETDATE()) <<
You might want to get enough basic SQL programming skills to write this
as:
COALESCE (foo_status_end_date, CURRENT_TIMESTAMP) >=3D CURRENT_TIMESTAMP
There is no such thing as a magical, universal "status": -- it is the
status of **something**. That getdate() is a proprietary syntax which
good SQL programmers avoid like lice. Learn the options in the
language so you can write them in SQL and not in some 3GL-style syntax.