Skip to content
Advertisement

How to get observation frequency counts from multiple dataset into one table?

I have a bunch of large datasets.

DS_1 (contains all unique IDs and names):

DS_2:

DS_3:

I’m looking to create a new dataset that shows frequency counts across the datasets (and lastly calculates Total_Obs). I would output something like this:

The datasets are fairly large. Is there a more efficient way to do this apart from concatenating the datasets and doing a frequency table? Or having to creating a bunch of sorted frequency tables, then merging by ID across all the datasets?

Advertisement

Answer

You can do below –

Also, you should always tag which Database you are using.

If the above SQL takes a lot of time, instead of t2 and t3 as inline queries you can consider creating aggregate observation tables with frequency/counts and having an index on id. That way when you join the observation aggregates with the primary table, the join can be faster based on indexes.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement