Is there a function/workaround to combining results from more than one table-CodePudding

I am using a dataset of a fictional bike sharing service. The data altogether emcompasses 12 months of data, split into 12 tables, one for each month. The columns and datatypes are consistent, and I am focusing on two columns - member-type and ride_length. The member_type column only has two values, 'member' or 'casual'. I want to compare the average ride length between member and casual riders for each month but have the results displayed on one table.

The results should look like this:

member_type | january | february | march
member | 100 | 50 | 30
casual | 60 | 45 | 25

The one solution I can think of is to combine all the tables into one big table, create a month column and go from there, but I don't want to do that if I can help it. Thank you in advance.

CodePudding user response：

The problem here is your data model. You should not have one table per month. All those ride values should be in the same table with a column for month. The fact that the columns are the same in all the month tables is a clue.

Then you'd execute a query like this:

select month, member_type, sum( ride_length ) as ride_length
  from mytable
 group by month, member_type

This will give you results like:

month      member_type   ride_length
---------- ------------- -----------
january    member        100
january    casual        60
february   member        50
february   casual        45
march      member        30
march      casual        25

That gives you the data that you want, though you really want to turn the month column into additional columns. That's a pivot and the syntax to do that will vary with your database.

CodePudding user response：

Use a CTE to build the table you don't want to create on the fly.

with combined as (
   select 1 as month, T.* from jan_tbl T union all
   select 2, T.* from feb_tbl T union all
...
)
select  from combined