count within union query
I've never done a count withing a union query and am running into issues
getting it done correctly.
I'm running the following query:
SELECT *
FROM
(
SELECT vg.vertrekluchthaven AS code, count(*) as countv
FROM tbl_vluchtgegevens vg
WHERE vg.vertrekdatum2 <=NOW()
GROUP BY vg.vertrekluchthaven
UNION
SELECT vg.aankomstluchthaven AS code, count(*) as counta
FROM tbl_vluchtgegevens vg
WHERE vg.vertrekdatum2 <=NOW()
GROUP BY vg.aankomstluchthaven
) vg
INNER JOIN tbl_luchthaven lh
ON lh.luchthavenID = vg.code
GROUP BY vg.code
ORDER BY lh.luchthavencode;
Ultimately I'm looking to figure out how to get a count for each code that
is in each of the union queries. When I run the code, I get a count for
the countv, but I don't get any kind of counta.
This is what I'm looking at obtaining:
+-------+-----------+-----------+-----------+
| CODE | Name | count v | count a |
+-------+-----------+-----------+-----------+
| AMS | LONG NAME | 5 | 8 |
| MSP | LONG NAME | 5 | 2 |
| WAS | LONG NAME | 2 | 0 |
+-------+-----------+-----------+-----------+
No comments:
Post a Comment