Efficient way to “multiply” a table? Almost feel like I need to “join” without a common field.

I’m kind of a newbie at SQL and don’t use it very often. The title is probably confusing, so here’s an explanation.

I’m working with demographic data. Table1 has columns State, County, Population, CarsOwned. There are ~3100 rows (1 for each county).

Table2 is a table I created myself and has columns Age, Income, Sex and Perc (which is a percentage of the specific demographic bucket). There’s 6 age “buckets”, 8 income “buckets” and 3 for Sex. So the table has 144 columns.

My goal is to divide each county’s population and CarsOwned into the same buckets and multiply them by appropriate percentages. The result table will have 3100×144 rows.

The only way I can think of doing it is creating 144 versions of Table1 (1 for each demographic bucket) and then Left Joining the Perc column. This way seems very tedious.

Hope this all makes sense and I appreciate any advice!

submitted by /u/Earthquake14
[link] [comments]


Go to Source of this post
Author Of this post: /u/Earthquake14
Title Of post: Efficient way to “multiply” a table? Almost feel like I need to “join” without a common field.
Author Link: {authorlink}