RagRe: How to select this ?

SELECT CAST(MONTH(DateCol) AS VARCHAR(2)) + '/' RIGHT(CAST(YEAR(DateCol) AS VARCHAR(2)),2)
SUM(CASE WHEN Work = 'Design' THEN 1 ELSE 0 END) as Design,
SUM(CASE WHEN Work = 'Programming' THEN 1 ELSE 0 END) as Programming
From SomeTable
GROUP BY
CAST(MONTH(DateCol) AS VARCHAR(2)) + '/' RIGHT(CAST(YEAR(DateCol) AS VARCHAR(2)),2)

The above query works fine . but when i try to change to below format :

SUM(CASE WHEN Work = 'Design' THEN 1 ELSE 0 END) as Design

in this query i how to insert this

SUM(CASE WHEN Work = 'Design' THEN (select distinct(act_point) from act_table) where act_id='1000' ELSE 0 END) as Design

when i use this i get the following error.

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How to solve this problem ?

Raghu:

Is what you are trying to do to add in a value of "the sum of all distinct 'act_point' values" for each 'DESIGN' record?

act_id act_point
-
1001 15
1000 10
1000 10
1002 5
1000 12
1000 7

For this mock-up act_table data you would try to add 10+12+7=19 for each 'DESIGN' record?

Dave