SQL SUM() All with check from other table

Question

Table con_projects

mysql> SELECT * FROM con_projects;
+----+---------------+---------------------+--------+
| id | project_name  | project_description | status |
+----+---------------+---------------------+--------+
|  1 | Project 1     | Description IS Here |      1 |
|  2 | Project 2     | Description IS Here |      0 |
|  3 | Project 3     | Description IS Here |      1 |
|  4 | Project 4     | Description IS Here |      0 |
|  5 | Project 5     | Description IS Here |      1 |
+----+---------------+---------------------+--------+

Here status 1=active and 0=inactive

mysql> SELECT * FROM con_transactions;
+------+------+---------------------+--------+
| t_id | p_id | date                | amount |
+------+------+---------------------+--------+
|   10 |    1 | 2016-02-17 19:24:05 | 1800   |
|   12 |    2 | 2016-02-18 11:40:13 | 200    |
|   17 |    3 | 2016-02-18 11:42:04 | 100    |
|   19 |    4 | 2016-02-18 11:45:43 | 1      |
|   20 |    5 | 2016-02-18 11:45:54 | 999    |
|   21 |    1 | 2016-02-18 11:46:02 | 1500   |
|   41 |    2 | 2016-02-18 17:23:14 | 500    |
|   42 |    3 | 2016-02-18 17:23:14 | 500    |
|   43 |    4 | 2016-02-18 17:23:15 | 500    |
|   44 |    5 | 2016-02-18 17:23:16 | 500    |
|   45 |    1 | 2016-02-18 17:23:16 | 500    |
|   46 |    2 | 2016-02-18 17:23:16 | 500    |
|   47 |    3 | 2016-02-18 17:23:17 | 500    |
|   48 |    4 | 2016-02-18 17:23:17 | 500    |
|   49 |    5 | 2016-02-18 17:23:18 | 500    |
|   50 |    1 | 2016-02-18 17:25:54 | 1000   |
|   51 |    1 | 2016-02-18 17:26:22 | 3000   |
|   52 |    2 | 2016-02-18 17:48:59 | 10     |
|   53 |    1 | 2016-02-18 17:48:59 | 10     |
|   55 |    1 | 2016-02-19 10:20:12 | 1000   |
+------+------+---------------------+--------+

Now I want to SUM() all amount from con_trnsactions whose p_id from con_projects status = 1

I tried this and many more

mysql> SELECT (SELECT SUM(t.amount) FROM con_transactions t WHERE p.id=t.p_id) as total FROM con_projects p WHERE status='1'
+-------+
| total |
+-------+
| 14120 |
+-------+

AND

mysql> SELECT SUM(amount) from con_transactions;
+-------------+
| SUM(amount) |
+-------------+
|       14120 |
+-------------+

Both Are Same . But The subtraction will be like this.

mysql> SELECT 1800+100+999+1500+500+500+500+500+500+1000+3000+10+1000;
+---------------------------------------------------------+
| 1800+100+999+1500+500+500+500+500+500+1000+3000+10+1000 |
+---------------------------------------------------------+
|                                                   11909 |
+---------------------------------------------------------+

here is the all active project (con_trsaction.p_id=con_projects.id AND con_projects.status=1) id (p_id) From con_trsanction.amount

SELECT sum(ct.amount) as amount FROM con_transactions ct left join con_projects cp on ct.p_id = cp.id where cp.status = 1 — roullie
– roullie, Commented Feb 19, 2016 at 6:37

Giorgos Betsos · Accepted Answer · 2016-02-19 06:35:24Z

2

Try this:

SELECT SUM(t.amount) 
FROM con_transactions t 
INNER JOIN con_projects AS p ON t.p_id = p.id
WHERE p.status = 1

It's a simple join operation. Only 'active' projects will be accounted for in the summation.

answered Feb 19, 2016 at 6:35

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

3 Comments

ABHIJIT Over a year ago

HOW Can I Select current month or last month All Transactions record. All Month are not 30 days. So How can I Select January or other month record with SELECT query SELECT * FROM transactions WHERE date > '2016-01-01 00:00:00' AND date < '2016-02-01 00:00:00';. this not smart please help me .

Giorgos Betsos Over a year ago

@ABHIJIT That's a different question. It's generally discouraged to annswer questions in comments. So try posting a new question.

ABHIJIT Over a year ago

stackoverflow.com/questions/35504755/… Help Please I wait for your ans

uglypointer · Accepted Answer · 2016-02-19 06:36:41Z

2

SELECT SUM(amount) from con_transactions LEFT JOIN con_projects ON con_projects.id = con_transactions.p_id WHERE con_projects.status = 1;

answered Feb 19, 2016 at 6:36

uglypointer

3591 silver badge14 bronze badges

Collectives™ on Stack Overflow

SQL SUM() All with check from other table

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related