Mysql - Join query no common column

Question

I have statement like this one :

SELECT
    COUNT(*)AS will
  FROM
    ae_orders o
  LEFT JOIN
    ae_orders_items oi ON oi.order_uid = o.uid
  WHERE
    po_date >= '2016-01-01' AND po_date <= '2016-01-31' AND status_text LIKE '%wil%'

This query is a count(*) so the result is only one field. But i would like to execute this query 6-7 time in one time changing only the (like '%will%') part of this statement and get the result of each count side by side.

Wanted result :

I try left join, full join like this

(
SELECT
  COUNT(*) AS will
FROM
  ae_orders o
LEFT JOIN
  ae_orders_items oi ON oi.order_uid = o.uid
WHERE
  po_date >= '2016-01-01' AND po_date <= '2016-01-31' AND status_text LIKE '%wil%'
) AS will
LEFT JOIN
  (
  SELECT
    COUNT(*) AS will
  FROM
    ae_orders o
  LEFT JOIN
    ae_orders_items oi ON oi.order_uid = o.uid
  WHERE
    po_date >= '2016-01-01' AND po_date <= '2016-01-31' AND status_text LIKE '%phi%'
) AS phil

I'm not really sure this is possible. If someone have a solution.

Solution

After reading some of your comments i found an other way to get and display my data.

Here is the statement to get my solution.

SELECT
  'Will' AS USER,
  COUNT(*) AS TOTAL
FROM
  ae_orders o
LEFT JOIN
  ae_orders_items oi ON oi.order_uid = o.uid
WHERE
  po_date >= '2016-01-01' AND po_date <= '2016-01-31' AND status_text LIKE '%wil%'
UNION
SELECT
  'Phil' AS USER,
  COUNT(*) AS TOTAL
FROM
  ae_orders o
LEFT JOIN
  ae_orders_items oi ON oi.order_uid = o.uid
WHERE
  po_date >= '2016-01-01' AND po_date <= '2016-01-31' AND status_text LIKE '%phi%'
UNION
SELECT
  'Peter' AS USER,
  COUNT(*) AS TOTAL
FROM
  ae_orders o
LEFT JOIN
  ae_orders_items oi ON oi.order_uid = o.uid
WHERE
  po_date >= '2016-01-01' AND po_date <= '2016-01-31' AND status_text LIKE '%Pet%'

Instead of getting result side by side i get result one above the other.

What you want is called pivot table or cross tabulated (crosstab) query. The linked topic deminstrates how to create such output if you know the columns in advance (fixed pivot) or you don't (dynamic pivot). — Shadow, Apr 21 '16 at 14:49
Have all table the same struct? `UNION` is used to combine the result from multiple `SELECT` statements into a single result set. — Ave, Apr 21 '16 at 14:53

score 1 · Accepted Answer · answered Apr 21 '16 at 15:26

As noted, your scenario is trying to pivot on a condition. Since all 3 queries are the same date range, just change to SUM( CASE/WHEN ) and you will have 1 row with all 3 counts.

SELECT
          SUM( case when status_text LIKE '%wil%' then 1 else 0 end ) as CountWill,
          SUM( case when status_text LIKE '%phi%' then 1 else 0 end ) as CountPhil,
          SUM( case when status_text LIKE '%Pet%' then 1 else 0 end ) as CountPeter
       from
          ae_orders o
             LEFT JOIN ae_orders_items oi 
                ON oi.order_uid = o.uid
       WHERE
              po_date >= '2016-01-01' 
          AND po_date <= '2016-01-31'

This is exactly the same solution as described in the linked duplicate topic. — Shadow, Apr 21 '16 at 15:27

Mysql - Join query no common column

Solution

1 Answers1