CODEWITHSUNDEEP
mysqlsql
beano
beano

Reputation: 952

MySQL Query: Inner Join, Count & Group By

I have the following query to obtain the count of entries for each formId:

SELECT DISTINCT
       formId,
       COUNT(entryID)
FROM approvalDetails
GROUP BY formId;

I would like to extend this query to pull the userEmail associated with the formId from another table (table #2 is called preferences)

I have tried the following, but the count values do not look correct.

SELECT approvalDetails.formId,
       COUNT(approvalDetails.entryID),
       preferences.userEmail
FROM approvalDetails
     INNER JOIN preferences ON approvalDetails.formId = preferences.formId
GROUP BY approvalDetails.formId,
         preferences.userEmail;

SAMPLE DATA

Table 1 - approvalDetails

formId    entryId
-         -
1         1
1         2
1         3
2         4
3         5

Table 2 - preferences (email address is not unique to the formId)

formId    userEmail
-         -
1         email1
2         email2
3         email3
3         email4
3         email4

Output required

  formId    Count (entryID)  userEmail
    -         -              -
    1         3              email1
    2         1              email2
    3         1              email3
    3         1              email4

Upvotes: 0

Views: 193

Answers (3)

NOPPADOL
NOPPADOL

Reputation: 26

SELECT ap.formId,
       ap.entryID,
       preferences.userEmail
FROM
(
    SELECT formId,
           COUNT(entryID) entryID
    FROM approvalDetails
    GROUP BY formId
) ap
INNER JOIN preferences ON ap.formId = preferences.formId
GROUP BY ap.formId,
         preferences.userEmail;

Upvotes: 1

zarruq
zarruq

Reputation: 2465

You can select distinct formId and emailId from table preferences in derived table and join it to table approval_Details as below.

SELECT t1.formId,
       count(EntryId) as EntryIdCount,
       t2.userEmail
FROM approvalDetails t1
JOIN
  (SELECT DISTINCT formId,
                   userEmail
   FROM preferences) t2 ON t1.formId = t2.formId
GROUP BY t1.formId,
         t2.userEmail

Result:

formId  EntryIdCount    userEmail
---------------------------------
1       3               email1
2       1               email2
3       1               email3
3       1               email4

DEMO

Upvotes: 0

Pramod S. Nikam
Pramod S. Nikam

Reputation: 4539

When You Use inner join that actually becomes equi join. Equi join intersects matching rows from all tables whoever participates in join. So It will form virtual table which will have rows commons according to join, So if first table has 10 rows and another table has 5 rows. but if join condition matches 4 rows, so the resultant virtual table only will have 4 rows.

So I think your count aggregate column showing only matching row.

Please use appropriate Left or Right outer join for your requirement.

Upvotes: 0

Related Questions