How to get the same result in single mysql query

Question

Right now I use two queries to count the number of contact from certain click. I've tried a lot of stuff like left join, right join, union, but I can retrieve the expected result.

The first query

SELECT offertId, count(*) as returnClicks from warehause where userId=8

returns

| offertId | returnClicks |
|    47    |      4       |

while the second

SELECT offertId, count(*) as returnContacts from contact where userId=8

returns

| offertId | returnContacts |
|    47    |        1       |

How can I make a single query to get this kind of result?

| offertId | returnClicks | returnContacts |
|    47    |      4       |        1       |

Don't worry, of course I don't need to count the contacts if I have no returnClick.

Answer 1

First things first: your queries are probably¹ invalid SQL because the column offertId that appears in the SELECT clause without being aggregated does not appear in the GROUP BY clause. Since version 5.7.5, MySQL rejects² such queries. Previous versions process them but they are free to return as offertId any value from the column offertId that is selected by the WHERE condition.

Are you sure you don't want to GROUP BY offertId in both queries?

I will assume both your queries contain GROUP BY offertId to get the correct results.

---

¹ The queries are valid and return the expected results if and only if offertId is functionally dependent on userId; i.e. all the rows having a certain value in userId also share the value of column offertId.

² Since version 5.7.5, using the default configuration, MySQL rejects the queries that use GROUP BY incorrectly. Previous versions of MySQL used to accept such queries but the values they return for columns that do not appear in the GROUP BY clause and are not used with GROUP BY aggregate functions were indeterminate.
To let the existing code run, the setting introduced by MySQL 5.7.7 can be turned off. It is not advisable to turn it off if you don't have old queries that run on the server; you better write valid SQL queries in your new code and don't rely on implementation details (they produce indeterminate results anyway).

---

The original solution, given the queries are changed to contain GROUP BY

You can JOIN the results returned by the two queries you already have:

SELECT *
FROM (
    SELECT offertId, count(*) as returnClicks from warehause where userId=8 GROUP BY offertId
) clicks
LEFT JOIN (
    SELECT offertId, count(*) as returnContacts from contact where userId=8 GROUP BY offertId
) contacts USING (offertId)

It will return all the rows generated by the first sub-query and the matching rows from the resultset generated by the second sub-query.

I assume the first sub-query returns in offertId all the values returned in offertId by the second sub-query. If they do the other way around then just replace LEFT JOIN with RIGHT JOIN.

Remark: If the sets of offertId values returned by the two sub-queries are not included one into the other then the above query does not return all the results you need. In this case you need to use FULL OUTER JOIN (it includes all rows from both sides) but, unfortunately, MySQL does not support it.

---

The no-GROUP BY solution

The op mentioned they don't need to use GROUP BY. In this case, each of their original queries returns at most one row. If the values returned in offertId by the sub-queries match, the code provided above also works and returns one row that contains offertId, returnClicks and returnContacts. If they don't match (or one of them doesn't return any rows) the final result set will miss the values produced by the second sub-query.

A possible solution to get all the values in the result set is to change the format of the result set.

This query:

SELECT 'clicks' as kind, offertId, count(*) as number from warehause where userId=8
UNION
SELECT 'contacts' as kind, offertId, count(*) as number from contact where userId=8

produces this result set:

| kind     | offertId | number |
| clicks   |    47    |    4   |
| contacts |    47    |    1   |

It contains all the rows returned by the two queries, no matter if they return 0 or 1 rows, no matter if the values of offertId match or not.

The column kind tells what sub-query generated the row, the column number tells the value produced by COUNT(*) from the corresponding sub-query.

The values can be analyzed, combined, processed in any way in the client code (assuming it is not a stored procedure and a more powerful language is used on the client side).

Answer 2

Try this:

SELECT COALESCE(t1.offertId, t2.offertId) AS offertId,
        returnClicks, returnContacts
FROM (SELECT offertId, count(*) AS returnClicks 
      FROM warehause 
      WHERE userId=8
) AS t1
CROSS JOIN (
  SELECT offertId, count(*) AS returnContacts 
  FROM contact 
  WHERE userId=8) AS t2

Both queries return always one row each, so you can use CROSS JOIN to bind them.

Demo here

Answer 3

Combine the 2 queries as subqueries with a join:

select t1.offertId, t1.returnClicks, t2.returnContacts 
from 
    (SELECT offertId, count(*) as returnClicks 
     from warehause 
     where userId=8) AS t1
inner join
    (SELECT offertId, count(*) as returnContacts 
     from contact where userId=8) AS t2

Answer 4

SELECT a.offertId,count(a.offertId) as returnContacts, count(b.offertId) as returnClicks from contact a join warehause b on a.userid = b.userid where userId=8

Answer 5

Like this:

SELECT *
FROM       (SELECT 1 as tmpId, offertId, count(*) as returnClicks from warehause where userId=8) t1
INNER JOIN (SELECT 1 as tmpId, offertId, count(*) as returnContacts from contact where userId=8) t2 on t1.tmpId = t2.tmpId

You might want to change the query above to a outer join if one or both sides of the join might return no rows.

PS: Posted from my cell, so it might have some minor bugs, since I wasn't able to test it.

Answer 6

This is dynamic for all users, using a correlated query.

SELECT t.offerTID,count(*) as returnClicks,
       (SELECT count(*) FROM returnContacts s
        WHERE t.userID = s.userID) as returnContacts
FROM returnClicks t

You can add a where clause at the end to filter your desired user

If each user has more then one offerTID , then you should add a group by clause like this:

SELECT t.offerTID,count(*) as returnClicks,
       (SELECT count(*) FROM returnContacts s
        WHERE t.userID = s.userID AND t.offerID = s.offerID) as returnContacts
FROM returnClicks t
GROUP BY t.offerTID