How to get SUM in Mysql from another table based on first table

Question

---------------colete--------------------
| id | id_comanda  | status | id_lista |
----------------------------------------
|    | 21775       | 0      | 3820     |
----------------------------------------
|    | 21776       | 0      | 3820     |
----------------------------------------
|    | 21777       | 0      | 3820     |
----------------------------------------


-----comenzi--------
| id  | taxComanda |
--------------------
|21775| 16.00      |
--------------------
|21776| 00.00      |
--------------------
|21777| 16.00      |
--------------------

I want to get SUM from column taxaComand from table comenzi by making the selection from column id_lista from table colete, so the SUM at the end to be 32.00

This is what i have, but is not good.:

SELECT a.id_comnda AS a_id_comanda, a.id_lista AS a_id_lista,
b.id AS b_id, b.taxaComanda AS b_taxaComanda
(SELECT sum(taxaComanda) FROM comenzi WHERE b.id = a.id_comanda AS totalTaxaComanda)
FROM colete a
INNER JOIN comenzi b ON b.id = a.id_comanda
WHERE a.id_lista = 3820
GROUP BY a.id_comanda

What is your expected output ?

sagi
– sagi

2016-11-16 15:22:07 +00:00
Commented Nov 16, 2016 at 15:22 — sagi
– sagi, Commented Nov 16, 2016 at 15:22
taxaComanda: 16.00 + 00.00 + 16.00 = 32.00

oxido
– oxido

2016-11-16 15:24:24 +00:00
Commented Nov 16, 2016 at 15:24 — oxido
– oxido, Commented Nov 16, 2016 at 15:24
Why? How does it make sense?

sagi
– sagi

2016-11-16 15:30:41 +00:00
Commented Nov 16, 2016 at 15:30 — sagi
– sagi, Commented Nov 16, 2016 at 15:30

Thomas G · Accepted Answer · 2016-11-16 15:29:25Z

1

Based on your comment I see no other answer than this :

SELECT SUM(b.taxComanda )
FROM colete a
INNER JOIN comenzi b ON b.id = a.id_comanda
WHERE a.id_lista = 3820

answered Nov 16, 2016 at 15:29

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

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

1 Comment

oxido Over a year ago

you are a god among mortals :D This mini query works, now i need to apply it in my query is slightly more complex. Thank you!

tyro · Accepted Answer · 2016-11-16 15:31:30Z

0

Using subquery and grouping id_lista:

SELECT id_lista, SUM(taxComanda ) as result,
FROM(
     SELECT id_comanda  , status , id_lista, taxComanda 
     from colete
     left join comenzi  on colete.id = comenzi.id_comanda)as sq
group by sq.id_lista

answered Nov 16, 2016 at 15:31

tyro

1,4781 gold badge17 silver badges34 bronze badges

1 Comment

oxido Over a year ago

Thak you @tyro, i will try this to! Great people here!

Collectives™ on Stack Overflow

How to get SUM in Mysql from another table based on first table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related