CODEWITHSUNDEEP
mysqlsql
mariakz
mariakz

Reputation: 19

How to join two queries to display as a single table iwth two headers in SQL?

I wanted to join these two block of codes into a single table when queried which displays the most common location for the pickup from different table which is (January and February) How would it be possible? These are my two codes:

SELECT pulocationid AS MostCommonPickupLocation
FROM jan
GROUP BY pulocationid
ORDER BY COUNT(*) DESC
LIMIT 1;

AND

SELECT pulocationid AS MostCommonPickupLocation
FROM feb
GROUP BY pulocationid
ORDER BY COUNT(*) DESC
LIMIT 1;

I want these two tables side by side and be able to do display them both by using one single query only

Expected oUTPUT

The expected out is to have two column headers which is January and February which contains the generated values from the above two separated queries I have provided. How to combine them together?

January February
3 43

Upvotes: 0

Views: 81

Answers (1)

Luuk
Luuk

Reputation: 14929

When you do this:

SELECT 
   jan.pulocationid AS January,
   feb.MostCommonPickupLocationFeb AS February
FROM jan
CROSS JOIN (
  SELECT pulocationid AS MostCommonPickupLocationFeb
  FROM feb
  GROUP BY pulocationid
  ORDER BY COUNT(*) DESC
  LIMIT 1
) feb
GROUP BY jan.pulocationid
ORDER BY COUNT(*) DESC
LIMIT 1;

you can expect to see output like:

January February
3 43

NOTE: When the query from January does not have results, there will be no output from this query.

Upvotes: 1

Related Questions