Q:

MySQL multiple results from one table in one statement

I'm trying to use multiple select query in two tables:
Table1 -> infoTab:
  theType   |          theTime    | theCount 
------------+---------------------+----------
 WithPinIt  | 2017-04-01 13:00:00 | 45
 WithPinIt  | 2017-04-01 13:00:00 | 100
 WithMinIt  | 2017-04-01 13:00:00 | 75
 WithQinIt  | 2017-05-01 13:00:00 | 45
 WithMinIt  | 2017-06-01 13:00:00 | 55

Table2 ->  defsTab:
   theId  |   theDefs   |   theType
----------+-------------+-------------
1         |  defQs      |  WithQinIt 
1         |  defOs      |  WithOinIt 
1         |  defJs      |  WithJinIt 

What I'm trying to get is a result like this:
          theTime    |  P  |  M 
---------------------+-----+-----
2017-04-01 13:00:00  | 145 | 
2017-04-01 13:00:00  |     | 75 
2017-06-01 13:00:00  |     | 55

So far I used this query:
SELECT   
    theTime,   
    (SELECT SUM(theCount) AS pSum FROM infoTab WHERE theType NOT IN (SELECT content FROM defsTab) AND theType LIKE '%P%' GROUP BY theTime) AS Ps,   
    (SELECT SUM(theCount) AS cSum FROM infoTab WHERE theType NOT IN (SELECT content FROM defsTab) AND theType LIKE '%C%' GROUP BY theTime) AS Cs   
FROM infoTab 

But it does not work... any help?

A:

it should work if you use CASE
select
theTime,
sum(
CASE 
WHEN theType like '%P%' THEN theCount
ELSE 0
END) as P,
sum(
CASE 
WHEN theType like '%C%' THEN theCount
ELSE 0
END) as C
from infoTab
where theType NOT IN (SELECT content FROM defsTab)
group by theTime;

