MySQL: Creating a dataset with multiple where clauses

I have different MySQL SELECT queries, all with different where statements. These produce totals, unique totals, totals of a certain type.

So for-instance:

Loc    | total | unique | missing

London | 10 | 5 | 2

New York |20 |10 |5

Currently I am running each of these Select queries separately, but 开发者_Python百科I'd rather run them all at once. Is there a way to do this in MySQL?

Something like:

SELECT
    Location
    Count(total)
    SELECT 
        count(unique)
    FROM
        .. my tables..
    WHERE
        .. where clause for unique part ..
    GROUP BY
        unique
FROM
    .. my tables ..
WHERE
    .. where clause for total part ..
GROUP BY
    total

Perhaps what you want is a UNION

SELECT * from table where...
UNION
SELECT * from table where...

Obviously the columns returned from each query need to be the same.

I expect a subquery (or several) is in order.

select location, (select count(total)  
                  from totalTable
                  where totalTable.location = locationTable.location) as total,
                 (select count(uniqe)
                  from uniqeTable
                  where uniqeTable.location = locationTable.location) as uniqe,
                 (select count(missing)
                  from missingTable
                  where missingTable.location = locationTable.location) as missing
from locationTable
where locationCriteria = searchCriteria

You can reference the same table in multiple subqueries, if necessary - just try to join them on ids, preferrably on something that's properly indexed. Obviously the where clauses will have to be adjusted as necessary.

Thank you all. For anyone else trying to get to grips with this, my eventual solution was:

SELECT
   Geo,
   total,
   SUM(unique) as unique
FROM
   (SELECT
      Geo,
      COUNT(total) AS total,
      0 unique,
    FROM
      -- my tables --
    WHERE
      -- where clause for totals --
    GROUP BY 
      Geo)
  UNION
    (SELECT
      Geo,
      0 total,
      COUNT(unique) AS unique,  
     FROM
       -- my tables --
     WHERE 
       -- where clause for unique --
     GROUP BY
       Geo)
) as tmptable
GROUP BY 
   Geo