How to find correct value in mysql

Question

I have a question about finding the correct value from my scenario. I have two table like this:

shift table

shift_id | shift_user | gate
-------- | ---------- | ------
   1     |     1001   |   1
   1     |     1001   |   2
   1     |     1001   |   3
   2     |     1002   |   1
   2     |     1002   |   2
   2     |     1002   |   3
   3     |     1003   |   1
   3     |     1003   |   3

Transaction Table

id   |  shift_id  |  sale   |   gate
-----|------------|---------|----------
  1  |     1      |  2000   |     1
  2  |     1      |  30000  |     2
  3  |     1      |  40000  |     3
  4  |     2      |  300    |     1
  5  |     2      |  4000   |     2
  6  |     2      |  3200   |     3
  7  |     3      |  5500   |     1
  8  |     3      |  100000 |     3

How to calculate sum of the sales for each shift_id?

Please provide me a good way with query.

Thanks a lot.

:)

EDIT

From the comment section of an answer it became clear that we need to get the sum of sales of a specific shift.

Answer 1

I found a solution for my question. I have used following query:

select sum(sale) from transaction inner join shift on shift.shift_id=transaction.shift_id and shift.gate=transaction.gate;

This returns the correct result. Thank for your reply, dear friends.

Answer 2

You need simple grouping

select shift_id, sum(sale)
from transactions
group by shift_id

If you need to get this result for a specific group, then do it like this

select shift_id, sum(sale)
from transactions
group by shift_id
having shift_id = 1

Answer 3

That's a simple grouping query. Try this:

SELECT shift_id, SUM(sale)
FROM transactions
GROUP BY shift_id;

Here is a fiddle

Answer 4

Use query like:

Select s.shift_id, sum(sale) from shift s INNER JOIN transaction 
ON s.shift_id=t.shift_id group by s.shift_id