pathros pathros - 5 months ago 10
SQL Question

How to add values when using UNION in MySQL?

I have this query where I try to union two queries, since each part has a different filter:

(/*First part where I want the current projects*/
SELECT p1.tipo as Tipo, COUNT(p1.tipo) AS Quantity
FROM projects AS p1
WHERE status IN ('REI','PRO')
GROUP BY Tipo
)
UNION
(/*Second part where I want those projects finished in the last year*/
SELECT p2.tipo as Tipo, COUNT(p2.tipo) AS Quantity
FROM projects AS p2
WHERE status IN ('TER')
AND COALESCE(p2.fech_fin,p2.fech_termino,p2.fech_proptermino) BETWEEN '2015-08-01' AND '2016-08-01'
GROUP BY Tipo
)


And I get the following results:

Type Quantity
COL, 97
IND, 147
COL, 16
IND, 31


And I see I get the results separated from each part. How can I get those results added like this

Type Quantity
COL, 113
IND, 178


?

Answer

Just sum them :

SELECT t.tipo,sum(t.quantity) as qty
    FROM (
    SELECT p1.tipo as Tipo, COUNT(p1.tipo) AS Quantity
    FROM projects AS p1
    WHERE status IN ('REI','PRO')
    GROUP BY Tipo
    UNION
    SELECT p2.tipo as Tipo, COUNT(p2.tipo) AS Quantity
    FROM projects AS p2
    WHERE status = 'TER'
    AND COALESCE(p2.fech_fin,p2.fech_termino,p2.fech_proptermino) BETWEEN '2015-08-01' AND '2016-08-01'
    GROUP BY Tipo
    ) t
GROUP BY t.tipo

Although as it looks, you only need one query:

    SELECT p1.tipo as Tipo, COUNT(p1.tipo) AS Quantity
    FROM projects AS p1
    WHERE p1.status IN ('REI','PRO') OR
          (p1.status = 'TER'
           AND COALESCE(p1.fech_fin,p1.fech_termino,p1.fech_proptermino) BETWEEN '2015-08-01' AND '2016-08-01')
    GROUP BY Tipo