October 18, 2016 at 10:33 am
I have what is a simple problem and easy solution, but for the life of me I cannot recall the exact syntax to get the results I want...
Here's the detail: I have 2 tables, one shows 6 'Activity types' such as "Letter, Note, Conversation, Meeting, etc." - only 6 records, 6 'Activities' I keep track of. The other table stores 10's of thousands of records showing these Activities over the years.
What I am trying to do is get a simple COUNT of the 6 Activities - in other words, through these years, how many Letters/Notes/Conversations/... etc. have I done. I thought this would be easy to do and I know years ago I knew how to engineer such a query. But now? Well, old man's mind-rot has erased my brain and I cant seem to engineer a good query. Here is my latest attempt that doesn't work...
SELECT
A.ActivityID,
A.ActivityDescription,
COUNT(*) OVER (PARTITION BY B.ActivityID) as CountActivities
FROM dbo.ActivityTypes A
INNER JOIN Activities B on A.ActivityID = B.ActivityID
This code gets close, but I wind up with thousands of records and since I am new to the OVER() clause, I think that's the screw up. But what I would like to see is simple:
1 Letter 250,000
2 Note 100,000
3 Conv. 50,000
4 Meeting 25,000....etc. for 6 'Type' records. (You get the idea.)
Can someone give this old man the proper syntax and/or SQL to achieve this?
Thanks very much!
October 18, 2016 at 10:53 am
The purpose of the windowed functions is to easily combine detail data with summary data, but you only want the summary data. Just use a simple GROUP BY instead of using the windowed functions.
SELECT
A.ActivityID,
A.ActivityDescription,
COUNT(*) as CountActivities
FROM dbo.ActivityTypes A
INNER JOIN Activities B on A.ActivityID = B.ActivityID
GROUP BY A.ActivityID, A.ActivityDescription
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 18, 2016 at 11:05 am
Thanks Drew... That worked swimmingly!!!
October 18, 2016 at 11:08 am
The window function brings the summary data into the detail row, but does not affect how many records are returned.
If you can't use a group by because you are mixing partitions, you can use a distinct clause.
These two queries return identical results, but the group by is more efficient.
SELECT TypeCount = COUNT(*) ,
type
FROM sys.objects
GROUP BY type
ORDER BY type;
SELECT DISTINCT
TypeCount = COUNT(*) OVER ( PARTITION BY type ) ,
type
FROM sys.objects AS o
ORDER BY type;
Wes
(A solid design is always preferable to a creative workaround)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply