CODEWITHSUNDEEP
phpmysqlsqlselectjoin
Mason Stedman
Mason Stedman

Reputation: 615

Trying to Merging Two grouped queries into one

I'm trying to get a list of all the clicks and sales associated with an account, with both tables having the account ID as an index. Right now I have:

SELECT ACCOUNT, SUM(REVENUE) AS R, COUNT(*) AS SALES
FROM CONVERSIONS
WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT

AND

SELECT ACCOUNT, COUNT(DISTINCT BIN_IP) AS CLICKS
FROM CLICKS
WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT

That gives me both lists, but then I have to do a ton of array manipulation to get them to uniquely line up. Trying to see if there's a way to join both queries, but thus far everything I've tried has failed.

Edit: For clarity, my issue with every join I've tried is I lose the case that theres sales and no clicks, or clicks and no sales. Joining them and checking that the table.ACCOUNT = table2.ACCOUNT only works if there's both clicks and sales present. This is essentially the part of the puzzle I'm struggling with.

Upvotes: 0

Views: 40

Answers (2)

neb
neb

Reputation: 26

it may be easier to add colums clicks to your first select, and column r and sales to the second one.( set these to 0 in the select)

Then you can do a union of the two selects and group by column account. you needs to use sum for the sales,clicks and r columns.

after this you can do a left join the accounts-table with the result to get all accounts (also those without clicks and sales)

Upvotes: 0

kjmerf
kjmerf

Reputation: 4345

If you are happy with the queries you have, you can use:

SELECT conv.ACCOUNT, conv.R, conv.SALES, clicks.CLICKS
FROM
(SELECT ACCOUNT, SUM(REVENUE) AS R, COUNT(*) AS SALES
 FROM CONVERSIONS
 WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT) conv
LEFT JOIN
(SELECT ACCOUNT, COUNT(DISTINCT BIN_IP) AS CLICKS
 FROM CLICKS
 WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT) clicks
ON conv.ACCOUNT = clicks.ACCOUNT
UNION
SELECT clicks2.ACCOUNT, conv2.R, conv2.SALES, clicks2.CLICKS
FROM
(SELECT ACCOUNT, COUNT(DISTINCT BIN_IP) AS CLICKS
 FROM CLICKS
 WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT) clicks2
LEFT JOIN
(SELECT ACCOUNT, SUM(REVENUE) AS R, COUNT(*) AS SALES
 FROM CONVERSIONS
 WHERE ACCOUNT = ? AND TIME BETWEEN ? AND ? GROUP BY ACCOUNT) conv2
ON clicks2.ACCOUNT = conv2.ACCOUNT

This is messy because mySQL does not have a FULL JOIN. So instead you have to use two left join and UNION. Note that the first set of joins, conv and clicks, will include cases where there are only conversion records and where there are records in both tables. The second set of joins, conv2 and clicks2, will return cases where there are only clicks.

Upvotes: 1

Related Questions