Multiple conditional counts on one field in SQL

Question

I have two tables where one contains author deatils and the other article details. One author may have many articles. In the article table is a field entitled "Decision"

The decision field can be "Accepted" or "Rejected". I want a query to output a table of author name and ID followed by a count of their number of submissions, accepts and rejects. The problem arises (I'm rather new to SQL so bear with me) because at the moment I am using a WHERE Article.Decision="Accept" or somelike, and can't find how to build in seperate dependancys on other generated fields without breaking syntax. Any help much appreciated!

Apologies, here is the current SQL statement which returns the authors which have a match and the count. Just an expression to return this table with authors which have a null count would be fine (then I could just append seperate queries to construct the whole thing)

SELECT Authors.[Corresponding Author URN], Authors.[Corresponding Author Surname], Count(Articles.Decision) AS CountOfDecision
FROM Authors LEFT OUTER JOIN Articles ON Authors.[Corresponding Author URN] = Articles.[Corresponding Author URN]
WHERE (((Articles.Decision)="Rejected"))
GROUP BY Authors.[Corresponding Author URN], Authors.[Corresponding Author Surname];

THE URN is the ID.

Answer 1

You could use a case statement:

select   sum(case when Decision = 'Rejected' then 1 else 0 end) as RejectedCount
,        sum(case when Decision = 'Denied' then 1 else 0 end) as DeniedCount
,        sum(case when Decision = 'Discarded' then 1 else 0 end) as DiscardedCount
,        sum(case when Decision = 'Buried' then 1 else 0 end) as BuriedCount

EDIT: In Access, you can use iif instead of case, like:

select   sum(iif(Decision="Delayed",1,0)) as DelayedCount
,        sum(iif(Decision="Ignored",1,0)) as IgnoredCount
,        sum(iif(Decision="Repulsed",1,0)) as RepulsedCount
,        sum(iif(Decision="Declined",1,0)) as DeclinedCount

Answer 2

SELECT 
         Authors.[Corresponding Author URN],
         Authors.[Corresponding Author SURNAME],
         SUM(CASE WHEN decision = 'Rejected' THEN 1 ELSE 0 END) Rejected,
         SUM(CASE WHEN decision = 'Accepted' THEN 1 ELSE 0 END) Accepted
FROM 
         Authors LEFT OUTER JOIN Articles ON Authors.[Corresponding Author URN] = Articles.[Corresponding Author URN]
GROUP BY 
         Authors.[Corresponding Author URN], Authors.[Corresponding Author Surname];

Answer 3

I want a query to output a table of author name and ID followed by a count of their number of submissions, accepts and rejects.

Start with a query based on your Articles table to present the counts for each author.

SELECT
    Articles.[Corresponding Author URN],
    Count(Articles.Decision) AS CountOfDecision,
    Sum(IIf([decision]="Accepted",1,0)) AS num_accepted,
    Sum(IIf([decision]="Rejected",1,0)) AS num_rejected
FROM Articles
GROUP BY Articles.[Corresponding Author URN];

If you save that one as qryArticleCounts, you can LEFT JOIN it with Authors.

SELECT
    a.[Corresponding Author URN],
    a.[Corresponding Author Surname],
    q.CountOfDecision AS num_submissions,
    q.num_accepted,
    q.num_rejected
FROM
    Authors AS a LEFT JOIN qryArticleCounts AS q
    ON a.[Corresponding Author URN] = q.[Corresponding Author URN]
ORDER BY a.[Corresponding Author Surname];

Change the ORDER BY expression as needed.

Answer 4

You could also use a crosstab, but I won't post the SQL for that, as I always just use the QBE to create crosstabs. The wizard should walk you through it. The result would be rows with the name columns first followed by a column for each value in the Decision column (an Accepted column and a Rejected column) and the count for each.