user1695602 user1695602 - 3 months ago 20
SQL Question

MySQL SUM(column) + (SUBQUERY WITH SUM)

I want to make a query that adds up points for a team. The points are added up by doing SUM on a column + a SUM from another table with the same team-id. I try to write it like this:

SELECT
k.id,
s.fylke,
s.Kommune,
s.Skolenavn,
k.schoolid,
k.number,
k.letter,
SUM(e.amount) + (SELECT SUM(poeng) FROM oppdrag WHERE klasseid=k.id ) AS poeng
FROM skoler AS s, klasser AS k, etappe AS e
WHERE s.id=k.schoolid AND k.id=e.klasseid AND e.year='2016'
GROUP BY k.id
ORDER BY poeng


The problem is that, when I write it in this fashion, it gives correct amount of points if the team has an entry in table "oppdrag" otherways it just returns NULL as points (poeng).

Answer

If your subquery returns NULL, this will result in trying to sum a number with NULL, which gives NULL back.

To fix this, you can try using the IFNULL function, that will replace it with a 0 if the query doesn't return any values:

SELECT 
    k.id, 
    s.fylke, 
    s.Kommune, 
    s.Skolenavn, 
    k.schoolid, 
    k.number, 
    k.letter, 
    SUM(e.amount) + IFNULL((SELECT SUM(poeng) FROM oppdrag WHERE klasseid=k.id ), 0) AS poeng 
FROM skoler AS s, klasser AS k, etappe AS e 
WHERE s.id=k.schoolid AND k.id=e.klasseid AND e.year='2016'
GROUP BY k.id 
ORDER BY poeng
Comments