02-06-2014 07:46 PM
I was attempting to use the query builder to get a two column summary table that showed the first column as the Age Group for my observations, and the second column being the number of times each age group shows up in the data. Here was the code the query builder created:
CREATE TABLE WORK.QUERY_FOR_CUMULATIVE_W_AGEGROUP AS
SELECT t1.AgeGroup, (Count(t1.AgeGroup)) AS COUNT_of_AgeGroup
FROM WORK.CUMULATIVE_W_AGEGROUP t1
GROUP BY t1.AgeGroup;
But when I run this code, the counts aren't correct. I can't figure out what it is trying to count, but they add up to 1,000. I have over 2 million rows, and I checked the table it calls, and every row has an entry, so I'm not sure why the count is off. I was able to get at the information using the summary table tools, but this technique is going to be important for me to use in other calculations. Any thoughts on what's causing it not to work as expected?
02-06-2014 09:31 PM
Check the name of the input table . . . .perhaps you are accidentally querying an already pre-summarized or filtered table?
Create a program node, make sure you have it set to run on the right server, and copy/paste and run (F8) the following code:
create table agg_count as
select count ( * ) as obs
If the counts are the same as the query you posted, then I suspect you are referencing the wrong table.
02-07-2014 09:38 AM
I'm afraid I don't have anything to add beyond what Fugue has said. It looks like the code should work, I don't see any reason why your results are wrong.
As Fugue says, make sure it's being run on the correct server, that's all I can think of.
02-10-2014 07:06 AM
You could try a different way to count your variable by creating a "listing" variable that is equal to 1, and then summing it up by the specific group.
1. Create a new variable using the "advanced expression" and enter: 1
2. Name it as "listing".
3. Pick the "sum" function from the summery options.
It will count the correct amount of OBS like a charm