Amount is incorrect when trying to join two tables

I am trying to get the correct sum from a column qty

when using joins. The problem occurs when I try to get timestamp

from the table paymentType

and join them to the table rowid

from orders

and paymentType

and then grouping timestamp

into days(day(from_unixtime(paymentType.timestamp)))

I want to get the amount qty

by hour using timestamp

in a table paymentType

, the only link is rowid

(this is the codeigniter cart module rowid

). The logical problem (for me at least) is that it contains more rows in the table orders

(as it does for each product) than the table paymentType

(this is just for tracking debit or cash usage). When I put these tables together, the amount for each hour is multiplied by each beat in orders.rowid <--> paymentType.rowid

.

I am sorry if the explanation is bad, but I hope it is quite clear that I could get help on this.

I have at least 10 queries that I have tried, but none works the way I want.

Below is a table orders

+---------+----+-------+-----+----------+------------------+----------------------------------+
| orderID | id | price | qty | subtotal |       name       |              rowid               |
+---------+----+-------+-----+----------+------------------+----------------------------------+
|       3 | 49 |    35 |   1 |       35 | Red Bull Stor    | f457c545a9ded88f18ecee47145a72c0 |
|       4 | 24 |    35 |   1 |       35 | Monster Energy   | 1ff1de774005f8da13f42943881c655f |
|       5 | 49 |    35 |   1 |       35 | Red Bull Stor    | f457c545a9ded88f18ecee47145a72c0 |
|       6 | 19 |    20 |   1 |       20 | Sprite 0.5L      | 1f0e3dad99908345f7439f8ffabdffc4 |
|       7 |  1 |    25 |   1 |       25 | Pringles         | c4ca4238a0b923820dcc509a6f75849b |
|       8 | 43 |    20 |   1 |       20 | Lån av stekovn   | 17e62166fc8586dfa4d1bc0e1742c08b |
|       9 | 46 |    35 |   1 |       35 | Burn             | d9d4f495e875a2e075a1a4a6e1b9770f |
|      10 | 49 |    35 |   3 |      105 | Red Bull Stor    | f457c545a9ded88f18ecee47145a72c0 |
|      11 | 49 |    35 |   1 |       35 | Red Bull Stor    | f457c545a9ded88f18ecee47145a72c0 |
|      12 | 29 |    25 |   1 |       25 | Potetskruer      | 6ea9ab1baa0efb9e19094440c317e21b |
|      13 | 16 |    20 |   1 |       20 | Coca-Cola 0.5L   | c74d97b01eae257e44aa9d5bade97baf |
|      14 | 46 |    35 |   1 |       35 | Burn             | d9d4f495e875a2e075a1a4a6e1b9770f |
|      15 |  1 |    25 |   1 |       25 | Pringles         | c4ca4238a0b923820dcc509a6f75849b |
|      16 | 18 |    20 |   1 |       20 | Eventyrbrus 0.5L | 6f4922f45568161a8cdf4ad2299f6d23 |
|      17 | 16 |    20 |   1 |       20 | Coca-Cola 0.5L   | c74d97b01eae257e44aa9d5bade97baf |
|      18 | 15 |    30 |   1 |       30 | Coca-Cola 1.5L   | 9bf31c7ff062936a96d3c8bd1f8f2ff3 |
|      19 | 19 |    20 |   1 |       20 | Sprite 0.5L      | 1f0e3dad99908345f7439f8ffabdffc4 |
|      20 | 50 |    20 |   1 |       20 | Stratos bar      | c0c7c76d30bd3dcaefc96f40275bdc0a |
+---------+----+-------+-----+----------+------------------+----------------------------------+

      

        
        
        
      

    

This is a table paymentType

+-----------+-------------+------------+----------------------------------+
| paymentID | paymentType | timestamp  |              rowid               |
+-----------+-------------+------------+----------------------------------+
|         3 | Kort        | 1424447799 | f457c545a9ded88f18ecee47145a72c0 |
|         4 | Kort        | 1424448791 | 1ff1de774005f8da13f42943881c655f |
|         5 | Kort        | 1424452822 | f457c545a9ded88f18ecee47145a72c0 |
|         6 | Kort        | 1424454483 | c4ca4238a0b923820dcc509a6f75849b |
|         7 | Kort        | 1424454665 | d9d4f495e875a2e075a1a4a6e1b9770f |
|         8 | Kontant     | 1424454799 | f457c545a9ded88f18ecee47145a72c0 |
|         9 | Kontant     | 1424454825 | f457c545a9ded88f18ecee47145a72c0 |
|        10 | Kort        | 1424454870 | 6ea9ab1baa0efb9e19094440c317e21b |
|        11 | Kontant     | 1424455510 | d9d4f495e875a2e075a1a4a6e1b9770f |
|        12 | Kort        | 1424455847 | c4ca4238a0b923820dcc509a6f75849b |
|        13 | Kontant     | 1424456025 | 6f4922f45568161a8cdf4ad2299f6d23 |
|        14 | Kontant     | 1424456099 | c74d97b01eae257e44aa9d5bade97baf |
|        15 | Kontant     | 1424456148 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 |
|        16 | Kontant     | 1424456242 | c0c7c76d30bd3dcaefc96f40275bdc0a |
|        17 | Kort        | 1424456266 | c74d97b01eae257e44aa9d5bade97baf |
|        18 | Kort        | 1424456445 | c0c7c76d30bd3dcaefc96f40275bdc0a |
|        19 | Kort        | 1424456964 | 70efdf2ec9b086079795c442636b55fb |
|        20 | Kort        | 1424457701 | 1ff1de774005f8da13f42943881c655f |
+-----------+-------------+------------+----------------------------------+

      

        
        
        
      

    

EDIT: There are more SQL queries I've tried so far, but they are the latest. I think these are the most "correct" ones.

select orders.rowid, concat(convert(paymentType.timestamp,CHAR(11))) timestamp, orders.qty, orders.name
from orders
join paymentType
on orders.rowid = paymentType.rowid
order by paymentType.timestamp;

select orders.rowid, hour(from_unixtime(concat(convert(paymentType.timestamp,CHAR(11))))), orders.qty, orders.name
from orders
join paymentType
on orders.rowid = paymentType.rowid
#where orders.name = '".stripslashes($name)."'
order by paymentType.timestamp
;
select orders.qty, orders.name, orders.rowid, paymentType.rowid, paymentType.timestamp
from orders, paymentType
where orders.rowid = paymentType.rowid;

select qty, name, hour(from_unixtime(timestamp)) hour, day(from_unixtime(timestamp)) day
from orders_w_time
where name = 'Red Bull Stor'
;

select sum(qty) from orders
inner join (select distinct rowid from paymentType) pt
on orders.rowid = pt.rowid
where orders.name = 'Pølse';

select sum(orders.qty) totalqty, orders.name, pt.timestamp timestamp from orders ord
inner join (select timestamp from paymentType where paymentType.rowid = ord.rowid) pt
on orders.rowid = pt.rowid
where orders.name = 'Red Bull Stor';
select * from
(
select rowid, timestamp from paymentType
group by hour(from_unixtime(timestamp))
) pt
left join
(
select sum(qty), name, rowid from orders
) ord
on ord.rowid = pt.rowid
;
Select
  paymentType.rowid,
  orders.name,
  orders.qty,
  paymentType.timestamp
From
  orders,
  paymentType
Group By
  day(from_unixtime(paymentType.timestamp));

select sum(orders.qty) Total
from orders
left join
(
select rowid,timestamp
from paymentType
) as paymet on orders.rowid = paymet.rowid
group by day(from_unixtime(paymet.timestamp))
;

select paymentType.rowid, ord.qty, timestamp
from paymentType
left join
(
select orders.rowid, qty
from orders
) as ord on ord.rowid = paymentType.rowid
;

      

        
        
        
      

    

The suggested result is to sum the qty column for "Red Bull Stor" (ie) for each day grouped by hour.