SQL group by and sum on different tables/views

Question

I have 2 views, A and B:

+--------------+------------------+
| Field        | Type             |
+--------------+------------------+
| id           | int(11) unsigned |
| vat_perc     | numeric          |
| vat_amount   | numeric          |
| project_id   | numeric          |
| ...                             |
+--------------+------------------+

I want a third view C which should basically have a similar structure but in the vat_amount field should contain the sum of all vat_amounts grouped by vat_perc and project_id from the two previous views.

C:

+--------------+------------------+
| Field        | Type             |
+--------------+------------------+
| id           | int(11) unsigned |
| vat_perc     | numeric          |
| vat_amount   | numeric          |
| project_id   | numeric          |
| ...                             |
+--------------+------------------+

For example, let's say A contains

+--------------+------------------+------------------+
| project_id   | vat_perc         | vat_amount       |
+--------------+------------------+------------------+
|  1           |  4%              | 10               |                  
|  1           |  5%              | 15               |                  
|  2           |  5%              | 15               |                  
|  3           |  4%              | 10               |                  
|              |                  |                  |                  
+--------------+------------------+------------------+

and B contains

+--------------+------------------+------------------+
| project_id   | vat_perc         | vat_amount       |
+--------------+------------------+------------------+
|  3           |  5%              | 10               |                  
|  2           |  4%              | 15               |                  
|  2           |  5%              | 15               |                  
|  1           |  4%              | 15               |                  
|              |                  |                  |                  
+--------------+------------------+------------------+

Then table C should contain

+--------------+------------------+------------------+
| project_id   | vat_perc         | vat_amount       |
+--------------+------------------+------------------+      
|  1           |  4%              | 25               |     
|  1           |  5%              | 15               |                  
|  2           |  4%              | 15               |     
|  2           |  5%              | 30               |                 
|  3           |  4%              | 10               |       
|  3           |  5%              | 10               |            
|              |                  |                  |                  
+--------------+------------------+------------------+

(I hope I made this clear enough, if you want an expanded example I could of course make it 'bigger')

Thanks

Answer 1

use

group by a.project_id ,a.vat_perc,b.project_id ,b.vat_perc

Answer 2

you can achieve that using union all and group by like this :

SELECT project_id,
       vat_perc,
       Sum(vat_amount) vat_amount
FROM   (SELECT project_id,
               vat_perc,
               vat_amount
        FROM   tableA
        UNION ALL
        SELECT project_id,
               vat_perc,
               vat_amount
        FROM   tableB) t
GROUP  BY project_id,vat_perc
ORDER  BY project_id 

order by clause can be removed if not needed.

Answer 3

use view:

CREATE VIEW C(project_id,vat_perc,vat_amount)
AS
WITH CTE as (
SELECT project_id,vat_perc,vat_amount FROM A
  UNION ALL
SELECT project_id,vat_perc,vat_amount FROM B)
SELECT project_id,vat_perc,SUM(vat_amount) AS vat_amount FROM CTE
GROUP BY project_id,vat_perc

Then only select values SELECT * FROM C ORDER BY project_id