CODEWITHSUNDEEP
mysqlsqljoin
ATMathew
ATMathew

Reputation: 12856

Joining two separate columns while using count()

Still learning MySQL, so bear with me.

So I have two tables: leads, which is:

id  dat          in_click_id 
55  2011-08-15      400 
10  2011-08-13      400
34  2011-08-13      420 
50  2011-08-12      420   
15  2011-08-14      425

The second table is called in_clicks:

id    location    keyword
400     KS         word1  
410     CO         word2
415     LA         word2
420     CA         word2
425     FL         word3

So I am trying to find out if a lead was associated with an in click. In other words, I am trying to get the following:

keyword     count(keyword)
word1        15
...          ...

So the keyword will be the word, and the second column will the aggregate number of leads from that keyword.

Since the data is in two separate tables, I need to use the join command, but I'm having some issues implementing it.

Here's one of my many attempts, which obviously doesn't work.

SELECT id, discriminator, create_date, in_click_id, COUNT(in_click_id) 
FROM leads 
WHERE create_date LIKE '%2011-08-17%' 
ORDER BY COUNT(in_click_id) DESC 
INNER JOIN in_click ON leads.in_click_id = in_clicks.id;

Help!?

Upvotes: 0

Views: 110

Answers (3)

Brent Stewart
Brent Stewart

Reputation: 1840

You need to use the Group By keyword:

SELECT in_clicks.keyword, COUNT(leads.in_click_id) 
FROM in_clicks  
INNER JOIN leads 
    ON leads.in_click_id = in_clicks.id
GROUP BY in_clicks.keyword
ORDER BY COUNT(leads.in_click_id)

Upvotes: 1

a'r
a'r

Reputation: 37009

Your SQL clauses are mis-ordered. A typical SQL query is in the following format

SELECT ...
FROM ...
    {joins}
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

For your query, you may want to try something like the following that will count the number of leads records associated with each keyword.

SELECT c.keyword, count(l.id) 
FROM in_click c
    INNER JOIN leads l ON l.in_click_id = c.id   
GROUP BY c.keyword
ORDER BY COUNT(l.id) DESC

Upvotes: 1

Dan
Dan

Reputation: 2195

You need to use GROUP BY, like this:

SELECT leads.id, COUNT(in_click_id) 
FROM leads 
INNER JOIN in_click ON leads.in_click_id = in_clicks.id
WHERE create_date LIKE '%2011-08-17%'
GROUP BY leads.id
ORDER BY COUNT(in_click_id) DESC

Upvotes: 4

Related Questions