This is my Table :
date(m/dd/yy) points Work
9/9/06 3 Design
9/10/06 3 Design
9/11/06 3 Programming
9/12/06 3 Design
10/9/06 3 Design
10/10/06 3 Design
10/11/06 3 Programming
10/12/06 2 Design
How to select in the below format
Month/Year Design Programming
9/06 9 3
10/06 8 3
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)
HTH, Jens K. Suessmeyer.
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 ?
Hi,
what about corss joining the Query ?
(...)
SUM(CASE WHEN Work = 'Design' THEN SubQuery.SomeValue ELSE 0 END) as Design
From SOmeTable
CROSS JOIN
(select distinct(act_point) SomeValue from act_table where act_id='1000') SubQuery
(...)
HTH, Jens K. Suessmeyer.