MySQL - combining two queries (UNION?)

Question

I have three tables: - - Order (gives me the date of the order) - Position of the order position (gives me the opportunity to associate the result of the activity with the date of the order) - Activity Item (gives me the result of the activity)

I am trying to display the results on a graph and can successfully create an array for all the results less <= 50 as Passed using the following query: -

SELECT
  YEAR( od_date ) AS Year,
  MONTH( od_date ) AS Month,
  test_name,
  pd_name,
  COUNT( test_result ) AS Passed
FROM tbl_lab_item
  INNER JOIN tbl_order_item
    ON tbl_order_item.od_item_id = tbl_lab_item.od_item_id
  INNER JOIN tbl_order
    ON tbl_order.od_id = tbl_order_item.od_id
WHERE tbl_order.od_date >= DATE_SUB(now(), INTERVAL 12 MONTH)
    AND test_name = 'Test'
    AND pd_name = 'Product'
    AND test_result <= 50
    AND od_customer_id = '4'
GROUP BY test_name, YEAR(od_date), MONTH(od_date)ASC

I can do this for unsuccessful results using the same query, but with Count (test_result) AS Error in Select and changing WHERE criteria to> 50.

I would like to do two more things in one query, if at all possible.

Firstly, I would like to combine the pass and the failure in the same request in order to get the month, the number of passes, the number of failures.

-, , , , , , , . UNION, , Null as Failed Pass , ?

, mysql?, , .

, ?

Answer 1

:

SELECT YEAR(od_date) AS `Year`, MONTH(od_date) AS `Month`, test_name, pd_name, 
       SUM(test_result <= 50) AS Passed, SUM(test_result > 50) AS Failes
FROM tbl_lab_item li
INNER JOIN tbl_order_item oi ON oi.od_item_id = li.od_item_id
INNER JOIN tbl_order o ON o.od_id = oi.od_id
WHERE o.od_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH) AND test_name = 'Test' AND 
      pd_name = 'Product' AND od_customer_id = '4'
GROUP BY test_name, YEAR(od_date), MONTH(od_date) ASC

Answer 2

SELECT
  YEAR( od_date ) AS Year,
  MONTH( od_date ) AS Month,
  test_name,
  pd_name,
  SUM( IF(test_result <= 50 , 1 , 0) ) AS Passed,
  SUM( IF(test_result > 50 , 1 , 0) ) AS Failed
FROM tbl_lab_item
  INNER JOIN tbl_order_item
    ON tbl_order_item.od_item_id = tbl_lab_item.od_item_id
  INNER JOIN tbl_order
    ON tbl_order.od_id = tbl_order_item.od_id
WHERE tbl_order.od_date >= DATE_SUB(now(), INTERVAL 12 MONTH)
    AND test_name = 'Test'
    AND pd_name = 'Product'
    AND od_customer_id = '4'
GROUP BY test_name, YEAR(od_date), MONTH(od_date)ASC

where

    id  |   month
-------------------
    1   |   1
    1   |   2
    1   |   3       
    1   |   4       
    1   |   5       
    1   |   6       
    1   |   7       
    1   |   8       
    1   |   9       
    1   |   10      
    1   |   11      
    1   |   12      

SELECT
  YEAR( od_date ) AS Year,
  MONTH( od_date ) AS Month,
  test_name,
  pd_name,
  SUM( IF(test_result <= 50 , 1 , 0) ) AS Passed,
  SUM( IF(test_result > 50 , 1 , 0) ) AS Failed
FROM tbl_lab_item
  INNER JOIN tbl_order_item
    ON tbl_order_item.od_item_id = tbl_lab_item.od_item_id
  INNER JOIN tbl_order
    ON tbl_order.od_id = tbl_order_item.od_id
  LEFT JOIN month ON months.month = MONTH(tbl_lab_item.od_date)
WHERE tbl_order.od_date >= DATE_SUB(now(), INTERVAL 12 MONTH)
    AND test_name = 'Test'
    AND pd_name = 'Product'
    AND od_customer_id = '4'
GROUP BY test_name, YEAR(od_date), MONTH(od_date)ASC