CODEWITHSUNDEEP
mysqlsqlselectmerge
Martin Hofman
Martin Hofman

Reputation: 53

SQL query - two select to one

How can I merge these two selects to one? As a result, I want column "POJ", "locked POJ" and "EV".

SELECT COUNT(VC) AS 'POJ', F.F_C.VC AS 'EV' FROM
F.F_D  LEFT JOIN F.F_C ON F.F_D.col_id = F.F_C.id
LEFT JOIN F.F_Z_D ON F.F_D.id = F.F_Z_D.id
where createdTime >= '2021-05-01' and createdTime <= '2022-05-31' and tTemplate = 'ZS' GROUP BY F.F_C.VC;

SELECT COUNT(VC) AS 'locked POJ', F.F_C.VC AS 'EV' FROM
F.F_D LEFT JOIN F.F_C ON F.F_D.col_id = F.F_C.id
LEFT JOIN F.F_Z_D ON F.F_D.id = F.F_Z_D.id
where createdTime >= '2021-05-01' and createdTime <= '2022-05-31' and tTemplate = 'ZS' and locked = 1 GROUP BY F.F_C.VC;

Upvotes: 0

Views: 37

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

Use conditional aggregation:

SELECT COUNT(VC) AS POJ, SUM(locked = 1) AS `locked POJ`, F.F_C.VC AS EV
FROM F.F_D
LEFT JOIN F.F_C ON F.F_D.col_id = F.F_C.id
LEFT JOIN F.F_Z_D ON F.F_D.id = F.F_Z_D.id
WHERE createdTime BETWEEN '2021-05-01' AND '2022-05-31' AND tTemplate = 'ZS'
GROUP BY F.F_C.VC;

Note that the only difference between the two queries is that the second one has the additional criterion locked = 1 in the WHERE clause. We can remove it and use the first query, along with a conditional count.

Upvotes: 1

Related Questions