April 22, 2012 at 8:46 pm
I want to have total count of one column in my table and I tried following query but doesn't seem to work!!
select WEB.DIM s.GROUP,
SUM(CASE WHEN WEB.ACTIVITY = profileupdate' THEN COUNT(WEB.ACTIVITY) ELSE 0 END) AS COUNT
from ACTIVITY_SUMMARY S join TYPE_DIM WEB
ON S.DIM = WEB.DIM
group by WEB.DIM,S.GROUP
I am not sure if I can have total counts like this in case statements!!! Please advice!!!
Thanks 🙂
April 22, 2012 at 9:21 pm
Not quite sure what you are attempting to accomplish based on your post. It would help if you would take the time to read the first article I reference below in my signature block. It will show you what to post and how to post it to get the best possible answers to your questions.
April 22, 2012 at 10:56 pm
@data Analyst:
Provide us more details. If you take time to craft your question well, you have more chances of correct answer.
What is the structure of the two tables and what you are trying to achieve?
======================================
Blog: www.irohitable.com
April 23, 2012 at 12:20 am
There are some fairly fundamental problems here that are not helping your case (no pun intended). Some of the column names are worse than unhelpful; they're reserved words and using them will cause you no end of headaches. For example I'd avoid calling things "group", and "count". Some people get around the reserved word problem by enclosing them in square brackets (i.e. [group], and [count]) but if it were me I'd much rather give them meaningful names that aren't reserved words.
Also bear in mind that your aim when writing SQL is to make it human readable. Merely writing something that compiles is not enough. When you have to come back and change the code in X months time, the compiler is not going to read it back to you and tell you what's going on. A great asset to your code will be comments (row level comments start with "--").
Next thing:
CASE WHEN WEB.ACTIVITY = profileupdate' ...might be missing an opening quote.
After all that and a bit of reformatting, I got to this point:
SELECT WEB.DIM
,s.[GROUP]
,SUM(CASE WHEN WEB.ACTIVITY = 'profileupdate'
THEN COUNT(WEB.ACTIVITY) --what are you wanting to count here?
ELSE 0
END
) AS [COUNT]
FROM ACTIVITY_SUMMARY S
JOIN TYPE_DIM WEB ON S.DIM = WEB.DIM
GROUP BY WEB.DIM
,S.[GROUP]
...which generated this error:
Msg 130, Level 15, State 1, Line 5
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
...So, I'm wondering if this is what you mean?
SELECT WEB.DIM
,s.[GROUP]
,SUM(CASE WHEN WEB.ACTIVITY = 'profileupdate'
THEN 1 --what are you wanting to count here?
ELSE 0
END
) AS [COUNT]
FROM ACTIVITY_SUMMARY S
JOIN TYPE_DIM WEB ON S.DIM = WEB.DIM
GROUP BY WEB.DIM
,S.[GROUP]
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
April 23, 2012 at 2:34 am
From GPO's last suggestion, wouldn't this be expressed a little more elegantly as follows:
SELECT WEB.DIM
,s.[GROUP]
,COUNT(WEB.ACTIVITY) AS [COUNT]
FROM ACTIVITY_SUMMARY S
JOIN TYPE_DIM WEB ON S.DIM = WEB.DIM AND WEB.ACTIVITY = 'profileupdate'
GROUP BY WEB.DIM
,S.[GROUP]
Note that you can of course COUNT (or SUM) based on a case.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply