CODEWITHSUNDEEP
mysqlsql
D Durham
D Durham

Reputation: 1341

MySQL COUNT??? help for query

I have the following query:

select AP.paper_id as id, 
concat(A.organization, A.country)as org, 
group_concat(SUBSTR(first_name, 1, 1), '. ', last_name SEPARATOR ', ') as initial
                from authors A
                                inner join authors__papers AP ON A.author_id = AP.author_id 
                                join tmp_orgs ORGS ON ORGS.paper_id = AP.paper_id 
                                where ORGS.organization=A.organization AND
                                AP.is_contact_author < 1
                                group by A.organization, AP.paper_id
                order by AP.paper_id, idx

It outputs data simlar to:

id     org   initial
1001   org1  J. Doe
1001   org1  J.Smith
1003   org2  A. Doe 
1004   org3  A. Smith
1004   org3  B. Doe
1006   org4  B. Smith
1007   org5  C. Doe

What I need is a 4th column that is a count of all orgs grouped by paper_id so the results would look like:

id     org   initial   org_count
1001   org1  J. Doe    2
1001   org1  J.Smith   2 
1003   org2  A. Doe    1
1004   org3  A. Smith  2
1004   org3  B. Doe    2
1006   org4  B. Smith  1
1007   org5  C. Doe    1

I tired a add sub-query:

(SELECT COUNT(DISTINCT(organization)) as dcnt from authors A INNER JOIN authors__papers AP on AP.author_id = A.author_id where AP.is_contact_author < 1 AND paper_id = ??? GROUP BY paper_id) as org_count

...but cannot figure out how to specify the paper_id for each row (the ??? in the query above. And is it better to to this as a join or column in the select, or does it really matter? I am not really a SQL guy, but have need for it because I am filling in for someone. Any help is appreciated!

Upvotes: 0

Views: 51

Answers (1)

Blank
Blank

Reputation: 12378

You are almost there, try this:

SELECT 
    AP.paper_id AS id, 
    CONCAT(A.organization, A.country) AS org, 
    GROUP_CONCAT(SUBSTR(first_name, 1, 1), '. ', last_name SEPARATOR ', ') AS initial,
    (
        SELECT COUNT(DISTINCT(organization)) AS dcnt
        FROM authors t1
        INNER JOIN authors__papers t2 ON t2.author_id = t1.author_id
        -- INNER JOIN tmp_orgs t3 ON t3.paper_id = t2.paper_id AND t3.organization = t1.organization 
        WHERE t2.is_contact_author < 1 
    ) AS org_count
FROM authors A
INNER JOIN authors__papers AP ON A.author_id = AP.author_id
INNER JOIN tmp_orgs ORGS ON ORGS.paper_id = AP.paper_id
WHERE ORGS.organization = A.organization 
AND AP.is_contact_author < 1
GROUP BY A.organization, AP.paper_id
ORDER BY AP.paper_id, idx

Upvotes: 1

Related Questions