Q:

Generating a table which shows Android and iOs visitors by month? MYSQL

I've resolved a part of the query displaying months and users for Android but I can't seem to find a way to join the users for iOS.
This is the table I'm trying to create form the query:
/Months/   IOS   /Android
|June     |324    |123  |
|July     |123    |23   |
|August   |521    |342  |
|September|712    |342  |

And this is what I've got
/Months   /  IOS  /
|June     |324    |
|July     |123    |
|August   |521    |
|September|712    |

This is the query I've been using
SELECT MONTHNAME(created_at) AS monthy ,
       COUNT(id) as ios 
FROM visitors 
WHERE MID(mobile_uuid,1,3) = 'ios' 
GROUP BY MONTH(created_at);

I want to generate data to populate a google chart showing the number of visitors on my application.
I've tried a couple of joins and I've been searching for 6 hours now.

A:

Try this query
SELECT MONTHNAME(created_at) MONTH,
(SELECT COUNT(mobile_uuid)  FROM VISITORS V2 
WHERE MID(mobile_uuid,1,3)='ios' 
AND MONTHNAME(V2.created_at)=MONTHNAME(V1.created_at)) AS IOS,
(SELECT COUNT(mobile_uuid)  from VISITORS v3 
WHERE MID(mobile_uuid,1,3)='adr' 
AND MONTHNAME(V3.created_at)=MONTHNAME(V1.created_at)) AS ANDROID
FROM VISITORS V1 GROUP BY MONTHNAME(created_at) ORDER BY created_at

Full code
CREATE TABLE visitors (
  id INT NOT NULL AUTO_INCREMENT,
  mobile_uuid VARCHAR(45) NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id));

INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrsdf873', '2016-06-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrsdf763', '2016-07-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrdg6h84', '2016-08-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iossdf87g', '2016-03-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrety895', '2016-03-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrd9g87d', '2016-03-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iossde88g', '2016-06-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iossge98g', '2016-07-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iossue78g', '2016-07-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('adrsref763','2016-07-15 23:27:07');
INSERT INTO visitors (mobile_uuid, created_at) VALUES ('iosscf67g', '2016-08-15 23:27:07');

SELECT MONTHNAME(created_at) MONTH,
(SELECT COUNT(mobile_uuid)  FROM VISITORS V2 
WHERE MID(mobile_uuid,1,3)='ios' 
AND MONTHNAME(V2.created_at)=MONTHNAME(V1.created_at)) AS IOS,
(SELECT COUNT(mobile_uuid)  from VISITORS v3 
WHERE MID(mobile_uuid,1,3)='adr' 
AND MONTHNAME(V3.created_at)=MONTHNAME(V1.created_at)) AS ANDROID
FROM VISITORS V1 GROUP BY MONTHNAME(created_at) ORDER BY created_at

The query worked for me

