CODEWITHSUNDEEP
mysqlsql
Radosław Andraszyk
Radosław Andraszyk

Reputation: 450

Mysql, left join and count conditions

I have fallowing SQL query (pseudo query):

SELECT 
    some columns [...]
    COUNT(clicks.id) AS clicks,
    COUNT(transactions.id) AS transactions 
FROM 
    campaign
LEFT JOIN   
    clicks ON clicks.key = campaign.key
LEFT JOIN (
    SELECT 
        id, key 
    FROM 
        transactions 
    GROUP BY 
        userkey 
    ) transactions ON clicks.key = transactions.key;
GROUP BY
    campaign.id

Query return good results. On example:

column | columns [..] | 34 | 10
column | columns [..] | 22 | 1
column | columns [..] | 34 | 17

So, records in table clicks they have transactions or a few transactions or they do not.

Haw Can I retun COUNT() clicks who have COUNT(transactions.id) = 0 and COUNT(transactions.id) > 0 ? On example:

column | columns [..] | 34 | 10 | 4 (count data from clicks table which have related transactions) | 30 (count data from clicks table which not have related transactions)
column | columns [..] | 22 | 1  | 6  | 16
column | columns [..] | 34 | 17 | 10 | 24

Tahnks for help.

@UPDATE:

I solved my problem adding second table. Now my SQL query looks like:

SELECT 
    some columns [...]
    COUNT(clicks.id) AS clicks,
    COUNT(transactions.id) AS transactions,
    COUNT(clicks_count.id) as witchout_transactions, 
    (COUNT(clicks.id) - COUNT(clicks_count.id)) as witch_transactions 
FROM 
    campaign
LEFT JOIN   
    clicks ON clicks.key = campaign.key
LEFT JOIN (
    SELECT 
        id, key 
    FROM 
        transactions 
    GROUP BY 
        userkey 
    ) transactions ON clicks.key = transactions.key
LEFT JOIN (
    SELECT 
        clicks.id, 
        COUNT(transactions.id) AS transactions 
    FROM 
        clicks 
    LEFT JOIN transactions ON clicks.key = transactions.key 
    GROUP BY clicks.id 
    HAVING transactions = 0
    ) clicks_count ON clicks_count.id = clicks.id 
GROUP BY
    campaign.id

Upvotes: 1

Views: 1795

Answers (2)

Joakim Danielson
Joakim Danielson

Reputation: 51973

What about adding a second join with the clicks table

JOIN (
    SELECT id 
    FROM clicks
    LEFT JOIN transactions ON clicks.key = transactions.key AND transactions.id != 0) clicks2 ON clicks.id = clicks2.id

And in the select clause use this to for the two columns 

SELECT 
    some columns [...]
    COUNT(clicks.id) AS clicks,
    COUNT(transactions.id) AS transactions
    COUNT(clicks2.id) as clicks_with
    clicks - clicks_with AS clicks_without

Upvotes: 1

D-Shih
D-Shih

Reputation: 46229

If I understand correctly, you can try to use CASE WHEN expression and COUNT

Because you didn' provide any sample data and expected result, so I can only provide pseudo-query.

SELECT...,
   COUNT(CASE WHEN [have transactions condition] then 1 end),
   COUNT(CASE WHEN [not have related transactions condition] then 1 end)

If that didn't help you, you can provide some data and expect result, I will edit my answer.

Upvotes: 2

Related Questions