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];