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

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
Asked:
paulo111
1 Solution
 
ispalenyCommented:
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

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