ktrinad Posted August 20, 2006 Share Posted August 20, 2006 By: symkobe - symkobe Distinct Count 2004-06-24 19:14I have a table like: Dep. Nation_ID What I want is how many ---------------- nations in each Dep. A ca The result should be like: A ca A cn Dep. Nation_Count A us -------------------- B us A 3 B us B 2 B ca -------------------- ---------------- Does anyone have some idea? Thanks very much. By: Kalpesh Chordia - kchordia RE: Distinct Count 2004-06-24 21:49Use group by clause in sql query selct dep, count(dep) as nation_count from <tablename> group by dep By: Chuck Deal - cdeal RE: Distinct Count 2004-06-25 06:05Is your report grouped by Dep and Nation_id? If so, you can use a variable to count the records. If that doesn't work, or you are not grouping, the other idea I have is that you use a Scriplet to help. If you use two hashmaps and one group (on Dep), you should be able to do it. I don't know if this solution is overkill, but it should at least work... HashMap 1 is keyed on the Dep. The value will be the DISTINCT count of Nation_id's HashMap 2 is keyed on NationId, the value is the count of nation_id's. The HashMap 2 will be reset each time the Dep Group is reset. The actual value for each Nation_id is not relavent at this time. Before you reset HashMap 2, find out how many entries it has. This is your distinct count of Nation_id for that group (which is by Dep) so then you can take that value and set HashMap 1. I'm shooting from the hip on this one, so it will need some work. By: symkobe - symkobe RE: Distinct Count 2004-06-25 06:39I am sorry for the unformated data. It should like: Dep. Nation_ID ---------------- A ca A ca A cn A us B us B us B ca ---------------- What I want to know is how many nations in each Dep. The result should be like: Dep. Nation_Count -------------------- A 3 B 2 -------------------- I will try the idea about group by Nation_id, and then count how many Nation_id groups in each Dep. Group. Thanks very much. By: sumit - sumitbhatnagar RE: Distinct Count 2004-08-12 08:00Hi Symkobe, I was wondering if you got around solving the Distinct problem. If yes, can u pl. help me out with the solution..... Waiting for your reply. Pl. feel free to email me @ sumit.bhatnagar@nasd.com thanks Sumit Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now