CODEWITHSUNDEEP
mysql
Ross
Ross

Reputation: 45

In MySQL, SELECT and COUNT from two différente tables

I have two tables in MySQL:

___Table1

| id | name |
|----|------|
| 98 | Fred |

___Table2

| link | amount |
|------|--------|
|   98 | 100.00 |
|   98 |  50.00 |

How can I SELECT all the items from ___Table1 and SUM datas from the ___Table2.

The desired output should be:

This is what I have tried so far:

SELECT 
SELECT SUM(amount) AS amount FROM ___Table2 WHERE ___Table2.link = ___Table1.id,
(SELECT * FROM ___Estimates ORDER BY EST_Id DESC)

Thanks.

Upvotes: 0

Views: 68

Answers (2)

xkx_0912
xkx_0912

Reputation: 26

SELECT
    t1.id AS id,
    t1.name as name,
    IFNULL ( SUM( t2.amount ), 0 ) AS account 
FROM
    ___Table2 t2
    RIGHT JOIN ___Table1 t1 ON t2.link = t1.id 
GROUP BY
    t2.link

Upvotes: 1

user9706
user9706

Reputation:

You could group by name instead but you didn't say if it was unique. If you just need a single user then add a where clause to select that user:

select name, sum(amount) as 'sum'
from ___Table1
join ___Table2 on ___Table1.id = ___Table2.link
group by ___Table1.id

Those table names are awful (you can't tell how many underscores by just looking at it), also it's a good idea to use the same name for the primary and foreign key (_id is the often used standard).

Upvotes: 1

Related Questions