CODEWITHSUNDEEP
mysql
Pathros
Pathros

Reputation: 10720

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

?

Upvotes: 1

Views: 26

Answers (2)

sagi
sagi

Reputation: 40481

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

Upvotes: 2

Marc B
Marc B

Reputation: 360592

UNION just glues two+ different queries together into a single result set. You can't do "cross union" aggregation with a single query. You'll have to wrap the union with an outer query:

SELECT sum(foo)
FROM (
    SELECT somefield AS foo FROM ...
    UNION ALL
    SELECT otherfield AS foo FROM ...
) AS x

Upvotes: 1

Related Questions