D Durham D Durham - 1 year ago 40
SQL Question

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!

Answer Source

You are almost there, try this:

    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