CODEWITHSUNDEEP
mysql
Brian
Brian

Reputation: 13573

How to combine the results of two MySQL queries into one?

Suppose I have a table like this:

enter image description here

How can I count the number of data that occur at the day of 2018-09-07 for each person, and the number of data that occur at the month 2018-09 for each person?

I mean I want to create a table like this:

enter image description here

I know that 

SELECT 
  name, 
  COUNT(*) AS day_count_2018_09_07
FROM data_table
WHERE 
  arrive_time >= '2018-09-07 00:00:00'
  AND
  arrive_time <= '2018-09-07 23:59:59'
GROUP BY name;

can generate the number of data that occur at the day of 2018-09-07 for each person, and 

SELECT 
  name, 
  COUNT(*) AS month_count_2018_09
FROM data_table
WHERE 
  arrive_time >= '2018-09-01 00:00:00'
  AND
  arrive_time <= '2018-09-30 23:59:59'
GROUP BY name;

can generate the number of data that occur at the month 2018-09 for each person.

But I don't know how to combine the above two queries so that day_count_2018_09_07 and month_count_2018_09 columns can be created in one query.

Here's the SQL fiddle where you can directly get the data in my question.

Upvotes: 2

Views: 51

Answers (3)

Shidersz
Shidersz

Reputation: 17190

What about something like this:

SELECT
  name,
  SUM(CASE WHEN (arrive_time BETWEEN '2018-09-07 00:00:00' AND '2018-09-07 23:59:59') THEN 1 ELSE 0 END) AS day_count_2018_09_07,
  SUM(CASE WHEN (arrive_time BETWEEN '2018-09-01 00:00:00' AND '2018-09-30 23:59:59') THEN 1 ELSE 0 END) AS month_count_2018_09
FROM
    data_table
GROUP BY
    name;

Upvotes: 0

Nick
Nick

Reputation: 147166

You can use conditional aggregation to get both results from the same query:

SELECT name, 
   SUM(CASE WHEN SUBSTR(DATE(arrive_time),1,7)='2018-09' THEN 1 ELSE 0 END) AS month_count_2018_09,
   SUM(CASE WHEN DATE(arrive_time)='2018-09-07' THEN 1 ELSE 0 END) AS day_count_2018_09_07
FROM data_table
GROUP BY name

Output:

name    month_count_2018_09     day_count_2018_09_07
Ben     3                       0
Jane    1                       1
John    3                       2

Upvotes: 3

Coral Kashri
Coral Kashri

Reputation: 3506

Try to combine them like that:

Select DayCounter.name, DayCounter.day_count_2018_09_07, MonthCounter.month_count_2018_09
from
(SELECT 
  name, 
  COUNT(*) AS day_count_2018_09_07
FROM data_table
WHERE 
  arrive_time >= '2018-09-07 00:00:00'
  AND
  arrive_time <= '2018-09-07 23:59:59'
GROUP BY name) as DayCounter
Inner Join
(SELECT 
  name, 
  COUNT(*) AS month_count_2018_09
FROM data_table
WHERE 
  arrive_time >= '2018-09-01 00:00:00'
  AND
  arrive_time <= '2018-09-30 23:59:59'
GROUP BY name) as MonthCounter

On DayCounter.name = MonthCounter.name

Upvotes: 2

Related Questions