Combine two queries in one query-CodePudding

I have this code for a chart and is working just fine. How can I combine this two separate queries into one query?

PS: I use a mysqli class if you asking why is $db->query() does here.

My code:

//Query one
    $qs=$db->query('SELECT 
     (DATE_FORMAT(regtime, "%M")) AS luna, 
      COUNT(id) AS devizieri FROM edev_useri WHERE groupid = 6 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
      GROUP BY (DATE_FORMAT(regtime, "%M")) 
      ORDER BY luna ASC');   
       
//Query two
     $qz=$db->query('SELECT 
     (DATE_FORMAT(regtime, "%M")) AS bluna, 
      COUNT(id) AS beneficiar FROM edev_useri WHERE groupid = 7 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
      GROUP BY (DATE_FORMAT(regtime, "%M")) 
      ORDER BY bluna ASC');

CodePudding user response：

Here is a solution.

SELECT t0.*, t1.devizieri, t2.beneficiar FROM
(SELECT 
 (DATE_FORMAT(regtime, "%M")) AS luna
    FROM edev_useri
  WHERE (groupid = 6 OR groupid = 7) AND YEAR(regtime) = YEAR(CURRENT_DATE) 
  GROUP BY (DATE_FORMAT(regtime, "%M")) 
  ORDER BY luna ASC ) AS t0
LEFT JOIN
(SELECT 
 (DATE_FORMAT(regtime, "%M")) AS luna, 
  COUNT(id) AS devizieri FROM edev_useri WHERE groupid = 6 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
  GROUP BY (DATE_FORMAT(regtime, "%M")) 
  ORDER BY luna ASC ) AS t1
ON t0.luna = t1.luna
LEFT JOIN
(SELECT 
    (DATE_FORMAT(regtime, "%M")) AS luna, 
    COUNT(id) AS beneficiar FROM edev_useri WHERE groupid = 7 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
    GROUP BY (DATE_FORMAT(regtime, "%M")) 
    ORDER BY luna ASC) AS t2
ON t0.luna = t2.luna

CodePudding user response：

$qs=$db->query('SELECT 
 (DATE_FORMAT(regtime, "%M")) AS luna, 
  COUNT(id) AS devizieri FROM edev_useri WHERE groupid = 6 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
  GROUP BY (DATE_FORMAT(regtime, "%M")) 
  ORDER BY luna ASC UNION SELECT 
         (DATE_FORMAT(regtime, "%M")) AS luna, 
          COUNT(id) AS devizieri FROM edev_useri WHERE groupid = 7 AND YEAR(regtime) = YEAR(CURRENT_DATE) 
          GROUP BY (DATE_FORMAT(regtime, "%M")) 
          ORDER BY luna ASC');