CODEWITHSUNDEEP
phpmysqlsqllaravel
b4dQuetions
b4dQuetions

Reputation: 1619

how to fetch sum every row mysql

Hy I have 2 table
1.application
id | name | status
====================
1 | morvick | complete
2 | siti | prosess
3 | boby | complete
`

2.application_test
id | application_id | test_id | result
======================================
1 | 1 | 1 | 70
2 | 1 | 2 | 80
3 | 1 | 3 | 90
4 | 2 | 1 | 60
5 | 2 | 2 | 80
6 | 2 | 3 | 70
7 | 3 | 1 | 90
8 | 3 | 2 | 70
9 | 3 | 3 | 60
10| 3 | 4 | 80
my Question is :
==================
1. how I can to get sum(result) on each test_id where status complete
2. how I can to get sum(result) on each test_id where status prosess

for example to be like this :
test_id | SUM(result = complete) | SUM(result = proses) |
1 | 90 | 50
2 | 80 | 40
3 | 90 | 60
4 | 80 | 70

Upvotes: 1

Views: 113

Answers (2)

Vipin Jain
Vipin Jain

Reputation: 3756

You can try two query to get sum of result

SELECT test_id , SUM (result) 
FROM application a 
JOIN application_test t 
ON t.test_id = a.id 
WHERE a.status = 'complete'
GROUP BY test_id 

SELECT test_id , SUM (result) 
FROM application a 
JOIN application_test t 
ON t.test_id = a.id 
WHERE a.status = 'prosess'
GROUP BY test_id

if you want in one query

SELECT test_id, 
SUM(IF(a.status='complete',result,0)) Completed , 
SUM(IF(a.status='process',result,0)) Process  
FROM  application  a 
JOIN application_test t
ON a.id=t.application_id 
GROUP BY t.test_id

Upvotes: 1

Zafar Malik
Zafar Malik

Reputation: 6844

Try below query-

SELECT test_id, 
SUM(IF(app.status='complete',apt.result,0)) AS complete_sum, 
SUM(IF(app.status='process',apt.result,0)) AS process_sum  
FROM application_test AS apt 
JOIN application AS app ON app.id=apt.application_id 
GROUP BY apt.test_id

Upvotes: 1

Related Questions