CODEWITHSUNDEEP
mysqlsubquery
ゆにた どうえ
ゆにた どうえ

Reputation: 93

select data from couple of subqueries in mysql

I have problem with mysql I need to select sums from some joined tables but I dont get the result I need. here's my query.

SELECT kelas.kelas as kelas, perpus.perpus as perpus
    FROM
    (SELECT b.id_alt, SUM(a.nilai_crips) AS kelas FROM crips AS a JOIN list AS b ON a.id_crips=b.id_crips where a.id_kriteria=34 group by b.id_alt
    )as Kelas,
    (SELECT SUM(a.nilai_crips) AS perpus FROM crips AS a JOIN list AS b ON a.id_crips=b.id_crips where a.id_kriteria=35 group by b.id_alt
    )as perpus
    group by kelas.id_alt

the 1st column shows the right result but the second column doesn't.

kelas | perpus
100   | 100
100   | 100
100   | 100
100   | 100
10    | 100
100   | 100
80    | 100
100   | 100

if I deleted the 1st subquery, the the column would return the right result.

perpus
100
100
100
100
100
100
100
76

what is wrong with my query? thank you in advance for the help.

Upvotes: 0

Views: 77

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Use a single SELECT, and conditionally add the values:

SELECT 
    b.id_alt, 
    SUM(IF(a.id_kriteria = 34,a.nilai_crips,0)) AS kelas,
    SUM(IF(a.id_kriteria = 35,a.nilai_crips,0)) AS perpus
FROM crips AS a 
JOIN list AS b 
ON a.id_crips = b.id_crips 
WHERE a.id_kriteria IN (34,35)
GROUP BY b.id_alt;

Upvotes: 2

Related Questions