• Status: Solved
• Priority: Medium
• Security: Public
• Views: 215

# Totals

I need to merge the following.... Below is the query that simply brings through the total number of contacts  per category

SELECT     COUNT(*) AS [Grand Total], Contact
FROM         DMR_Referrals
GROUP BY Contact, New_Fup, Contact
HAVING      (Contact IN ('A', 'D', 'C', 'M', 'B')) AND (New_Fup = 'N')

What I also need too do is with regards too the below

SELECT     COUNT(*) AS [Grand Total], Contact, refReason1
FROM         DMR_Referrals
GROUP BY Contact, New_Fup, Contact, refReason1
HAVING      (Contact IN ('A', 'D', 'C', 'M', 'B')) AND (New_Fup = 'N')

Show as a percentage each refReason1 of the grand total...

So if we say contact D has a grand total of 100, then I need refReason1 to be represented as the percentage of contact D.

Can anyone show me how to do this?
0
paulo111
1 Solution

Commented:
SELECT refReason1, Contact,100.*A.[Grand Total]/B.[Grand Total] [Grand Total Pct]
FROM
(
SELECT     refReason1, Contact, COUNT(*) AS [Grand Total]
FROM         DMR_Referrals
WHERE     (Contact IN ('A', 'D', 'C', 'M', 'B')) AND (New_Fup = 'N')
GROUP BY refReason1,Contact
) A
join
(
SELECT     Contact, COUNT(*) AS [Grand Total]
FROM         DMR_Referrals
WHERE     (Contact IN ('A', 'D', 'C', 'M', 'B')) AND (New_Fup = 'N')
GROUP BY Contact
) B ON A.Contact=B.Contact
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.