I am very new with SQL syntax.

Here I have two tables:

table_income
              ------ ------- ------- 
             |date  |Paper  |Plastic|
              ------ ------- ------- 
             |01-01 | $4    | $9    |
             |01-02 | $7    | $10   |
             |01-02 | $9    | $0    |
             |01-03 | $1    | $2    |
             
             Total    $21      $ 21
             Total Income $42

And the following table:

table_spend
              ------ ------- ------- 
             |date  | Bag   |Hat    |
              ------ ------- ------- 
             |01-01 | $1     | $5     |
             |01-02 | $9     | $10    |
             |01-02 | $5     | $0     |
             |01-03 | $2     | $2     |
             
             Total    $17       $17
             Total Spend 34

The result I am looking for is :

                   Income       ||      Spend   
            Paper       | $ 21  || Bag  | $ 17    |
            Plastic     | $ 21  || Hat  | $ 17    |
            ---------------------------------------
            Balance $ 8

Above will be layout in html.

SQL is as follow, but I am stuck in what to do.

 Select sum(table_income.paper), sum (table_income.plastic)
                from table_income 
                union select sum(table_spend.bag),sum(table_spend.hat)
                from table_spend

CodePudding user response：

2 things

1. sum (...) is not known, there should not be a space after the function name, change it to sum(...).

2. you might need to add aliasses to you columns, see DBFIDDLE, the names in you result will be taken from the first query in the union, that's why you will not see Column3 and/or Column4. But that will not solve the fact that you have one column with Paper and Bag values. (and Plastic and Hat)

Select 
   sum(table_income.paper) as Column1, 
   sum(table_income.plastic) as Column2
from table_income 

union 
select 
   sum(table_spend.bag) as Column3,
   sum(table_spend.hat) as Column4
from table_spend

EDIT: To get all four columns, you can do:

select 
   x.Column1,
   x.Column2,
   y.Column3,
   y.Column4
FROM
(Select 
   sum(table_income.paper) as Column1, 
   sum(table_income.plastic) as Column2
from table_income) x
cross join (
   select 
      sum(table_spend.bag) as Column3,
      sum(table_spend.hat) as Column4
   from table_spend
   ) y

DBFIDDLE