MySQL - joining two queries (UNION?)

I have three tables:- - Order (gives me the order date) - Order Line Item (gives me the ability to link the activity result to the order date) - Activity Item (gives me the activity result)

I am trying to plot results on a graph, and can successfully create an array for all 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 similar for Failed results using the same query but with Count (test_result) AS Failed in the Select, and changing the WHERE criteria to >50.

I'd like to accomplish two further things in one query if at all possible.

Firstly, I would like to be able to combine the pass and the fail into the same query so I'd get the month, count of pass, count of fail.

Secondly, in order to plot all this on a graph, I probably need to display all months even where there is no result for either Passes, Fails, or both. From my understanding of UNION, the queries need to be the same shape, so I could add a Null as Failed into the Pass query and vice versa, but how then would I merge this into a means of getting all the months to display?

I've looked here How to get a count even if there are no results corresponding mysql? to get all the months displaying, however I don't have the knowledge to integrate this into my queries.

Hopefully all this makes sense?

Answers


Try this:

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

For passed and failed you can do it

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

Remove the condition from where clause and put it in selection

For the second part create a table months

    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      

Now join it

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

Need Your Help

Running GWT SuperDevMode in IDEA on Win8

gwt windows-8 intellij-idea gwt-super-dev-mode

I am trying to configure my GWT project to run in SuperDevMode and experience severe problem during server run time.

gsm ATD command to check my balance

c# bluetooth gsm at-command 32feet

I am trying to write a program that makes a phone call via bluetooth