MySQL query on multiple tables

Question

Greets! I have 12 tables, one for each month of the year:

January

+----+-----+  
| id | sale|  
+----+-----+  
|  1 | 250 |  
|  3 | 500 |
|  5 | 200 |  
|  7 | 100 |  
+----+-----+

February

+----+-----+  
| id | sale|  
+----+-----+  
|  1 | 350 |  
|  2 | 400 |
|  3 | 500 |  
|  4 | 800 |  
+----+-----+

etc.

I need to do a query where the result is something like this:

Annual Sales
+----+-----------+-----------+
| id | Sales_Jan | Sales_Feb |
+----+-----------+-----------+
|  1 |       250 |       350 |
|  2 |         0 |       400 |
|  3 |       500 |       500 |
|  4 |         0 |       800 |
|  5 |       200 |         0 |
|  7 |       100 |         0 |
+----+-----------+-----------+

Where the matching ids from both tables do not duplicate and the missing ids from other months are shown by putting a 0 or any other symbol indicating that there was not any sales that month from that id.

Thank you very much!

next time make a months table and reference them with month_id — Sam
– Sam, Commented Dec 4, 2013 at 2:22
Agreed! I am not sure why you are storing your sales in separate database tables for each month, but it generally flies in the face of entity design and data modeling. A sale is a sale, the month it occurs in is an attribute of a sale. Tables should represent entities, not attributes. — JC.
– JC., Commented Dec 4, 2013 at 2:23
I know, is part of the engineering process, but the database was handed to me like that and I do not have permission to modify it. — user3063952
– user3063952, Commented Dec 4, 2013 at 2:33

Gordon Linoff · Accepted Answer · 2013-12-04 02:41:26Z

3

You can approach this using union all and aggregation:

select id,
       sum(case when month = 'Jan' then sale else 0 end) as Jan_Sale,
       sum(case when month = 'Feb' then sale else 0 end) as Feb_Sale,
       . . .
       sum(case when month = 'Dec' then sale else 0 end) as Dec_Sale
from ((select 'Jan' as month, id, sale from January) union all
      (select 'Feb' as month, id, sale from February) union all
      . . .
      (select 'Dec' as month, id, sale from February)
     ) t
group by id;

answered Dec 4, 2013 at 2:41

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3063952 Over a year ago

You are a saint! Thank you very much! There's a missing "as" before the t, but i do not know if I have to edit that or you. I'm new around, as you can see. Again, thank you very much.

Gordon Linoff Over a year ago

@user3063952 . . . I use as for column aliases but not for table aliases. This is the standard for Oracle, although the as is usually optional for most database engines.

user3063952 Over a year ago

By any chance, would you know how to this same thing on Ms-access? I was told that the php is with MySQL, but there's gonna be a console application with Ms-access. Thank you in advance.

Gordon Linoff Over a year ago

@user3063952 . . . MS Access uses iif() instead of case. I think the union all will work there.

Collectives™ on Stack Overflow

MySQL query on multiple tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related