CODEWITHSUNDEEP
mysqlcountgroup-byinner-join
elong
elong

Reputation: 71

inner join + count + group by

I'm have trouble counting/grouping the results of an inner join

I have two tables

  1. results_dump: Which has two columns: email and result (the result value can be either open or bounce)

  2. all_data: Which has three columns: email, full_name and address

The first goal is to query the result_dump table and count and group the number of times the result is "open" for a specific email.

This query works great:

SELECT `email`, COUNT(*) AS count
FROM `result_dump`
WHERE `date` = "open" 
GROUP BY `email`
HAVING COUNT(*) > 3
ORDER BY count DESC

The second goal it to take those results (anyone who "open" more then 3 time) and pull in the 'full_name' and 'address' so I will have details on who opened an email 3+ times.

I have this query and it works as far as getting the data together - But I can't figure out how to get the COUNT, HAVING and ORDER to work with the INNER JOIN?

SELECT *
FROM all_data
INNER JOIN result_dump ON 
all_data.email = result_dump.email
where `result` = "open"

Upvotes: 0

Views: 25052

Answers (3)

Amjath Khan
Amjath Khan

Reputation: 531

This is Works Fine...! Try to this..

SELECT title.title
       ,count(*)
       ,title.production_year
       ,title.id as movie_id
       ,title.flag as language
       ,movie_info.info
FROM title INNER JOIN movie_info ON title.id=movie_info.movie_id;

Upvotes: -1

Akash KC
Akash KC

Reputation: 16310

Try with following query:

SELECT * FROM all_data AS a
INNER JOIN 
(SELECT * FROM result_dump where email IN 
                                    (SELECT `email`
                                     FROM `result_dump`
                                     WHERE `date` = "open" 
                                     GROUP BY `email`
                                     HAVING count(email) >3
                                     ORDER BY count(email) DESC)) AS b
ON a.email = b.email
WHERE b.`result` = "open"

Upvotes: 1

geekman
geekman

Reputation: 2244

SELECT email,name,count(*)
FROM all_data
INNER JOIN result_dump ON 
all_data.email = result_dump.email
where `result` = "open" 
group by result_dump.email
having count(*)>3
ORDER by count DESC

Nothing wrong with this one I think.

Upvotes: 4

Related Questions