469,955 Members | 2,388 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,955 developers. It's quick & easy.

Analytical function with where clause

My record set will look like this

ENAme -Part Time- Dept
qwe - Y - 10
fjj - N - 10
ghsd - N - 10

How to count using Analytical function count of parttime by dept
Ex :
dept 10 prttime - Y -count 1
dept 10 prttime - N -count 2

how to write select Statement ?
Sep 18 '06 #1
2 4814
select dept,partTime,count(*) from table_name group by dept,partime order by count(*)
Sep 19 '06 #2
The real power of analytical functions shows through when you want to see the full details of each record in the set along side totals and subtotals that you would normally have to calculate using a group by clause. Essentially analytical functions save you the trouble of joining the results of a group by query back to the original set of records that it was grouping.
A normal group by query as written by the first replier to your message would be simpler for getting the exact result set that you are requesting. But....
Say for instance you wanted to see the grouping count along side the full details (I changed the names to make them align better) like this...

AAA - Y - 10 - 1
BBB - N - 10 - 2
CCC - N - 10 - 2

For this you would write something like....

select a.*,
count() over (partition by Dept, Part time) as MemberCount
from
employees a
Oct 18 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Yuri G. | last post: by
2 posts views Thread by Agoston Bejo | last post: by
20 posts views Thread by Brian Tkatch | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.